Backend5 min read

Data Modeling

Design databases for correctness and performance - entity-relationship modeling, normalization, denormalization trade-offs, and schema evolution.

Data ModelingSchema DesignERDNormalizationDatabase Design

skill: Data Modeling category: Backend tags: [Data Modeling, Schema Design, ERD, Normalization, Database Design] readingTime: 5

Schema design decisions are nearly permanent - changing a normalized schema to denormalized (or vice versa) after millions of rows is expensive. Get the core entity model right upfront. Normalize for write integrity first, then denormalize specific read paths where performance data demands it.

Workflow

Model entities and relationshipsEntity-Relationship modeling captures the business domain before choosing a database. Clear entities, attributes, and relationships prevent schema redesigns later.
Normalize to 3NFThird Normal Form (3NF) eliminates redundancy that causes update anomalies - changing one fact in one place should update everywhere it appears.
Strategically denormalize for read performanceNormalized schemas require joins - joins are fast for small data but expensive for large analytical queries. Denormalize when profiling confirms the join is the bottleneck.
Implement soft deletes correctlyHard deletes (DELETE) break foreign key references and lose audit history. Soft deletes add deleted_at - the row stays but is filtered out by default.
Design for schema evolutionSchemas evolve as products grow. Design choices today determine migration pain tomorrow - additive changes (new columns, new tables) are safe; destructive changes (rename, drop) require care.

Output Format

Data modeling: E-R analysis before schema, normalization to 3NF, strategic denormalization with profiling, soft deletes with partial index, and additive schema evolution.

Entities: nouns in the domain
Relationships: 1:N foreign key, M:N junction table
Normalize: every column depends only on the PK
Denormalize: precomputed totals, counts (profiling first)
Evolution: nullable columns, JSONB for flexibility

Examples

Input

Output

Edge Cases & Gotchas

Polymorphic associations - A "comments" table that belongs to either a Post or a Product (polymorphic) breaks FK integrity. Use separate comment tables (post_comments, product_comments) or a generic events table.
Temporal data - Data that changes over time (price history, role changes) needs temporal tables - store effective_from and effective_to dates and query at a point in time with WHERE effective_from <= $date AND effective_to > $date.
Tree hierarchies - Storing trees (categories, org charts) in relational DBs is hard. Choose: adjacency list (simple, recursive CTEs needed), nested sets (fast reads, expensive writes), or closure table (most flexible).
UUID vs sequential ID - UUIDs are globally unique and can be generated client-side, but fragment B-tree indexes (random inserts). Use ULIDs or UUIDv7 (time-sorted) for the performance of sequential IDs with UUID uniqueness.

When to Break These Rules

Use a document database (MongoDB) instead of a relational model when your entities are genuinely schema-less and hierarchical - forcing a document into rows and columns adds complexity without benefit.
Use a graph database (Neo4j) instead of relational for heavily interconnected data where the relationships are the primary query subject - social graphs, recommendation engines.

Reference Files

https://www.postgresql.org/docs/current/ddl.html
https://martinfowler.com/articles/evodb.html