Introduction to Data Modeling

Data modeling works by splitting the real-world facts an application cares about into entities (things with independent identity) and relationships (how those things connect), so that every fact is stored exactly once and re-assembled on demand through keys — which is precisely what stops the same fact from drifting out of sync in two places.

The payoff is concrete and mechanical, not aesthetic. A model is a contract: it decides which columns exist, which value uniquely identifies a row (the primary key), and which column in one table points at the key of another (the foreign key). Get that decomposition right and a customer changing their email touches one row; get it wrong and the same email is duplicated across thousands of order rows, any of which can disagree.

Why one flat table breaks

Suppose an e-commerce team starts with the obvious thing: one wide spreadsheet-style table where each row is a line item on an order. It looks fine for the first ten rows.

orders_flat
+---------+------------+-------------------+-----------+-----------+-------+-----+
| order_id| cust_name  | cust_email        | product   | unit_price| qty   | ... |
+---------+------------+-------------------+-----------+-----------+-------+-----+
| 1001    | Asha Rao   | asha@mail.com     | USB-C Hub | 1200      | 1     |     |
| 1001    | Asha Rao   | asha@mail.com     | HDMI Cable| 300       | 2     |     |
| 1002    | Asha Rao   | asha@gmail.com    | USB-C Hub | 1200      | 1     |     |
+---------+------------+-------------------+-----------+-----------+-------+-----+

Three failures are already baked in, and they are the classic anomalies every data model exists to prevent:

• Update anomaly. Asha changed her email, so order 1002 says asha@gmail.com while order 1001 still says asha@mail.com. The database now holds two different "truths" for one person. To fix it you must find and rewrite every row she ever appears in.
• Insertion anomaly. You cannot record a new product ("USB-C Hub costs 1200") until somebody actually orders it, because the price only lives inside order rows.
• Deletion anomaly. Delete the last order containing the HDMI Cable and you have silently erased the fact that the HDMI Cable exists at all, including its price.

Worked example: model the same facts

Data modeling fixes this by asking, for each fact: what entity does this fact describe? The customer's name and email describe a Customer. The product's name and price describe a Product. The order's date describes an Order. The quantity describes neither alone — it is a fact about the pairing of an order and a product, so it lives on the join. That reasoning produces four tables.

customers                         products
+---------+----------+----------------+      +-----------+-----------+-----------+
| cust_id | name     | email          |      | prod_id   | name      | unit_price|
+---------+----------+----------------+      +-----------+-----------+-----------+
| C1      | Asha Rao | asha@gmail.com |      | P1        | USB-C Hub | 1200      |
+---------+----------+----------------+      | P2        | HDMI Cable| 300       |
                                             +-----------+-----------+-----------+
orders                            order_items
+----------+---------+------------+   +----------+---------+-----+
| order_id | cust_id | order_date |   | order_id | prod_id | qty |
+----------+---------+------------+   +----------+---------+-----+
| 1001     | C1      | 2026-06-20 |   | 1001     | P1      | 1   |
| 1002     | C1      | 2026-06-28 |   | 1001     | P2      | 2   |
+----------+---------+------------+   | 1002     | P1      | 1   |
                                      +----------+---------+-----+

Notice what just happened to each anomaly. Asha's email is now one row in customers — update it once and orders 1001 and 1002 both follow, because they only store her cust_id (C1), not her email. A new product can be inserted into products with zero orders. Deleting all of Asha's orders leaves the HDMI Cable row untouched.

The original wide row is not lost — it is derivable. To rebuild the receipt for order 1001 you follow the keys, step by step:

1. Take order_id = 1001 from orders; read its cust_id = C1.
2. Follow C1 into customers → Asha Rao, asha@gmail.com.
3. Find rows in order_items where order_id = 1001 → (P1, qty 1) and (P2, qty 2).
4. Follow P1 → USB-C Hub @ 1200; follow P2 → HDMI Cable @ 300.
5. Compute line totals: 1×1200 = 1200 and 2×300 = 600 → order total 1800.

In SQL that traversal is a join with the foreign keys as the seams:

SELECT c.name, p.name AS product, oi.qty, p.unit_price,
       oi.qty * p.unit_price AS line_total
FROM   orders o
JOIN   customers   c  ON c.cust_id  = o.cust_id
JOIN   order_items oi ON oi.order_id = o.order_id
JOIN   products    p  ON p.prod_id  = oi.prod_id
WHERE  o.order_id = 1001;
-- Asha Rao | USB-C Hub  | 1 | 1200 | 1200
-- Asha Rao | HDMI Cable | 2 |  300 |  600

Why the naive flat table is wrong: it stores unit_price next to every order line, so the price of "USB-C Hub" is recorded redundantly on every order that ever included it. The modeled version stores that price once in products and lets the join supply it. The flat table is not just inelegant — it is structurally incapable of representing a single source of truth.

Pitfalls

• Hiding a many-to-many as repeated columns. Modeling an order with product_1, product_2, product_3 columns caps the order at three items and makes "how many of product P2 sold this week?" impossible to query cleanly. The fix is the join table (order_items) — any N:M relationship needs its own table.
• Storing a derived value as if it were a fact. Putting order_total in the orders table seems convenient, but it duplicates information the line items already imply. Now a refunded item can leave the total wrong. Store the inputs and compute totals; only cache a total if you maintain it deliberately, never by accident.
• Using a meaningful column as the primary key. Keying customers on email breaks the day someone changes their email or two people share one (family) inbox. Surrogate keys (cust_id) stay stable while the business attributes around them churn.
• Modeling the screen instead of the domain. If the model mirrors today's UI form rather than the underlying entities, the first feature change forces a schema migration. Model the things that are true regardless of how they are displayed.
• Confusing the conceptual model with the physical schema. "Customer places Order" is a conceptual statement; whether cust_id is a BIGINT or a UUID, indexed or not, is physical. Deciding both at once starts arguments about column types before the relationships are even agreed.

Takeaways

• A data model is a decomposition: every real-world fact gets one home, and keys reconnect the homes at query time. Redundancy is the disease; the model is the cure.
• The three anomalies — update, insertion, deletion — are the concrete test of whether a model is sound. If any can occur, a fact is stored in more than one place.
• Entities hold facts about a single thing; relationships (especially many-to-many) often need their own table. The quantity on an order line belongs to the order×product pair, not to either alone.
• Choose stable, meaningless primary keys and store inputs rather than derived results, so business change touches data in one place, not thousands.

Re-authored and deepened for this guide. The anomaly framing and entity/relationship distinction draw on C. J. Date, An Introduction to Database Systems (8th ed.); Garcia-Molina, Ullman & Widom, Database Systems: The Complete Book; and Peter Chen's 1976 paper "The Entity-Relationship Model — Toward a Unified View of Data" (ACM TODS). The worked e-commerce schema, traced rows, SQL query, and SVG were authored for this page.

🎯 STANDOUT elevation: Why / example / when-not / failure / panel / drills — Introduction to Data Modeling

Why this exists / the decision it encodes

Model before CREATE TABLE so every real-world fact has one home. The model is a contract: which entities, which primary keys, which foreign keys reconnect facts. Flat tables fail not aesthetically but via update/insert/delete anomalies — two emails for one person, inability to store a product without an order, loss of product metadata when the last order dies.

Worked example with numbers or traced SQL/FD

orders_flat: order 1001 Asha asha@mail.com USB-C 1200×1; 1002 Asha asha@gmail.com …
Update anomaly: two different emails for Asha
Model fix:
  customers(C1, Asha, asha@gmail.com)
  products(P1 USB-C 1200)(P2 HDMI 300)
  orders(1001,C1,2026-06-20)(1002,C1,2026-06-28)
  order_items(1001,P1,1)(1001,P2,2)(1002,P1,1)
Rebuild receipt 1001 via joins: 1×1200 + 2×300 = 1800
Email update once on C1; both orders follow.

When NOT / named alternative

Do not model the screen (today's form fields) instead of the domain. Do not use email as PK (churns). Do not store derived order_total without a deliberate maintenance story. When NOT to fully normalize: audited price-at-sale on order_line is a different fact from catalog price; measured read-path denorm with sync ownership.

Failure mode / ops fingerprint / interview trap

Fingerprint: customer email updated in app UI but half of historical order rows still show old email. Interview trap: "we denormalized for performance" with no measured QPS or consistency owner.

Domain judgment (K11 theory-bridge / K12 concurrency / K13 query-judgment)

K11: anomalies are the practical face of functional dependencies the key fails to respect. K13: join-on-keys is the query shape cost you accept to buy single-source-of-truth writes.

Hostile-panel drills (with model answers)

Q1. Name the three anomalies on the flat orders table and one sentence each.
Model answer: Update: same fact repeated → partial updates disagree. Insert: cannot store product without an order row. Delete: deleting last order erases product existence/price.

Q2. Why does qty live on order_items, not orders or products?
Model answer: Quantity is a fact about the order×product pair, not about either entity alone.

Q3. Surrogate vs natural key for customers?
Model answer: Prefer stable surrogate cust_id; natural keys like email change and break FKs and history.