Schema Design (Normalization, 1-n/n-n Relationships, Denormalize)

1. Normalization and 3 types of anomalies

An unnormalized design (for example, storing customer_name, customer_email, customer_phone directly inside the orders table without a separate customers table) causes 3 issues:

• Update anomaly: the same piece of information (customer phone number) is duplicated across multiple rows (every order belonging to that customer). Updating the phone number requires an UPDATE across all related rows; missing a single row creates data inconsistency.
• Insertion anomaly: a new customer's information cannot be recorded without at least one existing order, because customer details have no place to exist outside the orders table.
• Deletion anomaly: deleting a customer's only order permanently deletes that customer's information as well, even though the customer still exists in reality. Solution: split into separate tables and reference via foreign keys:

customers: id, name, email, phone
orders: id, customer_id, product_name, product_price, quantity

2. 1-to-N and N-to-N relationships

• 1-to-N (1 customer, many orders): add the foreign key directly to the "many" table (orders.customer_id).
• N-to-N (1 order contains multiple products, 1 product appears in multiple orders): a single column cannot store multiple product_id values simultaneously (storing strings like "1,5,9" breaks efficient JOIN, WHERE, and indexing capabilities and is strongly discouraged). A junction table is required:

order_items: id, order_id, product_id, quantity, price_at_order_time

Each row in order_items represents a specific (order_id, product_id) pair.

3. When to denormalize (deliberate and intentional data duplication)

Reason 1: historical data must remain immutable. For example, price_at_order_time in order_items: if not stored separately, pulling directly from products.price every time past invoices are viewed means future price adjustments will retroactively corrupt historical invoice records (distorting revenue calculations, etc.). "Current price" and "price at time of order" are two distinct business concepts, not poor design duplication.

Reason 2: read performance optimization. If a table must JOIN multiple other tables just to retrieve 1–2 columns, and that table is SELECTed with extremely high frequency while rarely UPDATEd, denormalizing (persisting those columns directly in the primary table) eliminates repeated, expensive JOIN operations.

Real-world example combining both reasons: a comment system displaying each commenter's name and avatar.

• Pure normalization: comments (id, post_id, user_id, content) + users (id, name, avatar_url), requiring a JOIN on every render.
• Denormalization: comments (id, post_id, user_id, content, user_name, user_avatar_url), snapshotting the name/avatar at comment time with no JOIN required.
• The trade-off: when a user updates their name/avatar, older comments retain the old name/avatar (unless background synchronization jobs are executed). Many large-scale platforms (Facebook, Reddit) accept this trade-off because name/avatar update frequency is vastly lower than read frequency.

4. Aside: Graph databases solve a different problem than denormalization

• Graph databases (such as Neo4j) excel when the problem space involves deep, multi-hop relationship traversals (finding friends of friends of friends, recommendation engines, fraud ring transaction analysis). SQL requires recursive or multi-level self-JOINs that degrade rapidly with depth; Graph databases store direct relationships (edges) between nodes, making deep traversals significantly faster.
• The comment/avatar example above is merely a single-hop JOIN executed at high frequency, not a complex relationship problem. A Graph database offers no benefit here; denormalization or caching (Redis) is the appropriate solution.
• Summary: Graph databases optimize for relationship depth and complexity, whereas denormalization/caching optimizes for the read throughput of simple, highly repeated queries.