Schema Design & Normalization

Schema design is a storage decision

Before indexes, buffer pools, or replication matter, you choose what lives in which table and how rows relate. That shape determines write amplification (how many places one fact is stored), read fan-out (how many joins a dashboard needs), and what constraints the engine can enforce cheaply at insert time.

For writing queries against a schema, see the SQL roadmap. Here the question is: why is the data stored this way?

Entity-relationship thinking

Model entities (nouns: Customer, Order) as tables and relationships as foreign keys or junction tables. Cardinality drives shape:

Relationship	Storage pattern
1:1	FK on either side, or shared PK
1:N	FK on the "many" side (orders.customer_id)
M:N	Junction table (order_products(order_id, product_id))

Interview signal: read an unfamiliar ERD and explain which anomalies the split prevents.

Normalization: eliminating redundant facts

Storing customer_email on every orders row repeats a fact. That causes:

• Update anomaly — email change must touch every order row
• Insertion anomaly — can't record a customer with zero orders
• Deletion anomaly — deleting the only order erases the customer record

1NF — atomic columns (no comma-separated tags in one cell).
2NF — no partial dependency on a composite key.
3NF — no transitive dependency (non-key → non-key).

You rarely recite formal definitions; you point to the anomaly the split fixes.

Denormalization is deliberate

Normalization optimizes write correctness; denormalization optimizes read latency. Caching order_count on customers, maintaining a materialized search index, or embedding related fields for a hot read path are valid when:

• Reads dominate writes on that path
• You have a strategy to keep the copy consistent (trigger, CDC, batch rebuild)
• The join cost at scale exceeds redundancy cost

Senior answer to "should we normalize?" — "What's the read/write ratio and consistency requirement?"

Schema-on-write vs schema-on-read

Relational engines enforce schema-on-write — bad rows fail at insert. Document stores often defer validation to read time (schema-on-read). Neither is universally better; it's a trade-off between enforcement at the boundary vs deployment agility.

Interview gotchas

• Treating denormalization as "doing it wrong"
• Designing for 3NF then joining six tables on every API read without measuring
• Ignoring hot columns — wide rows with large TEXT/JSON bloat pages and hurt cache efficiency

Where this goes next

Keys, Constraints & Index Design covers how to enforce relationships and speed up the access paths your schema implies.