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?

GraphQL vs REST for Data Access Cheat Sheet

CAP Theorem Cheat Sheet

Explore Topics

#DataModelingBestPractices#DataModelingBestPracticesCheatSheet#Database#Intermediate#CorePrinciples#WellModeledSchema#CommonAntiPatterns#JSON#Databases#CheatSheet#SkillVeris

Sri Hayavadhana Info-Tech

Professional Web Designing Services