Data Modeling Best Practices Cheat Sheet
Covers practical guidelines for choosing keys, normalization level, indexing, and schema evolution when designing production database schemas.
2 PagesIntermediateMar 2, 2026
Core Principles
Guidelines that hold up across most relational schemas.
- Model access patterns first- Design tables around how the application actually queries data, not just the abstract entity relationships
- Choose the right key type- Use surrogate integer/UUID keys for internal joins; avoid using mutable business data (email, SSN) as a primary key
- Normalize until it hurts, denormalize until it works- Start normalized (3NF) for correctness, then selectively denormalize hot read paths once you've measured a real bottleneck
- Enforce constraints in the database- Use NOT NULL, UNIQUE, CHECK, and foreign keys — application-only validation gets bypassed by scripts, migrations, and bugs
- Use appropriate data types- Store dates as DATE/TIMESTAMP not strings, money as NUMERIC/DECIMAL not FLOAT, and booleans as BOOLEAN not 0/1 integers
Well-Modeled Schema
Constraints and types that enforce correctness at write time.
sql
CREATE TABLE accounts ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), email CITEXT UNIQUE NOT NULL, balance_cents BIGINT NOT NULL DEFAULT 0 CHECK (balance_cents >= 0), status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'suspended', 'closed')), created_at TIMESTAMPTZ NOT NULL DEFAULT now());CREATE TABLE transactions ( id BIGSERIAL PRIMARY KEY, account_id UUID NOT NULL REFERENCES accounts(id), amount_cents BIGINT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now());CREATE INDEX idx_transactions_account_created ON transactions (account_id, created_at DESC);
Common Anti-Patterns
Modeling mistakes that create pain later.
- Entity-Attribute-Value (EAV)- Storing arbitrary key/value rows instead of real columns; kills query performance and type safety, avoid unless truly unavoidable
- God table- One giant table with dozens of nullable columns for many unrelated purposes; split into focused, properly related tables
- Storing money as float- Floating-point rounding errors corrupt financial calculations; use DECIMAL/NUMERIC or integer cents
- No foreign keys 'for performance'- Skipping FK constraints to save a few cycles usually just relocates data-integrity bugs into production incident reports
- Polymorphic associations without a discriminator- A column that can reference different tables depending on context breaks referential integrity; use separate join tables per type instead
JSON Column vs Normalized Table
When a JSONB column is appropriate versus a real table.
sql
-- OK: JSONB for genuinely variable, rarely-queried attributesCREATE TABLE products ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, attributes JSONB -- e.g., {"color": "red", "size": "M"} varies per category);-- Better: normalize anything you filter, sort, or join on frequentlyCREATE TABLE product_prices ( product_id INT REFERENCES products(id), currency CHAR(3) NOT NULL, amount_cents BIGINT NOT NULL, PRIMARY KEY (product_id, currency));-- Index into JSONB only when necessaryCREATE INDEX idx_products_color ON products ((attributes->>'color'));
Pro Tip
Treat every 'just add a JSON blob column' decision as normalization debt you're taking on — it's fine for truly sparse/variable attributes, but the moment you're filtering, joining, or aggregating on a JSON field regularly, promote it to a real column before query performance and data quality both degrade.
Was this cheat sheet helpful?
Explore Topics
#DataModelingBestPractices#DataModelingBestPracticesCheatSheet#Database#Intermediate#CorePrinciples#WellModeledSchema#CommonAntiPatterns#JSON#Databases#CheatSheet#SkillVeris
