Data Modeling

The problem: one big orders table

Data modeling is deciding what your system stores and how the pieces point to each other. Say a food-ordering app's first version keeps each order as one row, with everything the app shows copied in:

order  customer  email      restaurant
501    Ana       ana@x.com  Pizza Roma
502    Raj       raj@x.com  Pizza Roma
503    Ana       ana@x.com  Sushi Go
504    Mei       mei@x.com  Pizza Roma

It works on day one. Then Ana changes her email, and two rows have to change, 501 and 503. A regular with 200 orders has 200 copies, and no row is Ana herself.

Now Pizza Roma renames itself Forno Roma, and its name sits in three rows. Say the rename updates only this month's orders, to stay quick: order 504 changes, while 501 and 502 keep the old name. The app now shows two names for one restaurant, and a search for Forno Roma, the only way to find its orders without a restaurant id, finds 1 order of 3. One fact stored in several places and changed in only some is an update anomaly.

Two more traps: a new restaurant with no orders yet has nowhere to be stored (an insertion anomaly), and deleting order 503, Sushi Go's only one, deletes everything the app knew about Sushi Go (a deletion anomaly).

Normalize: store each fact once

The fix is to keep each restaurant once, in a restaurants table, and have each order keep only the restaurant's id:

restaurants        orders
id  name           order  restaurant_id
10  Forno Roma     501    10
11  Sushi Go       502    10
                   503    11
                   504    10

A rename is now one row, and every order shows the new name by looking up its id (a join). This is normalization: each fact stored once, everything else pointing to it by id. Updating every copy instead still rewrites thousands of rows for a busy restaurant and misses a copy typed differently, like "pizza roma"; a nightly repair job leaves two names until it runs, and can't tell which one is new.

The normal forms say this formally: a column holds one value, not a list (first normal form), and every other column depends on the whole key (second) and on nothing but the key (third). A restaurant's name depends on the restaurant, not the order, so it doesn't belong in orders.

Entities and attributes

Which nouns get tables? A thing with facts of its own, that other records point to, is an entity: customers, restaurants, dishes and orders each get a table. One fact about a thing is an attribute, a column of its table: a customer's email, a dish's price, an order's delivery address.

The address is a judgment call: in a first version it's one fact about the order, where this meal goes. If customers later save addresses to pick from, those get a table of their own.

Relationships: one-to-many and many-to-many

A restaurant has many dishes, and each dish belongs to one restaurant: a one-to-many relationship. The link goes on the many side, as a restaurant_id column on each dish, and Forno Roma's menu is every dish with restaurant_id 10. A list of dish ids on the restaurant row would break the one-value rule; keeping both the list and the column stores one fact twice, and the two drift apart.

Orders and dishes are many-to-many: Ana's order 501 has a Margherita and a Pepperoni, and the Margherita is in orders 501, 502 and 504. Neither side can hold the link: a dish_id on the order allows one dish per order, and an order_id on the dish lets each dish be ordered once, ever. The answer is a join table, order_items, with one row per dish in an order:

order_id  dish_id  qty
501       100      1
501       101      1
502       100      2
503       102      3
504       100      1

Its key is the pair (order_id, dish_id), and it holds facts that belong to neither side alone, like qty. Students and courses have the same shape: enrollments, with the date each student enrolled.

When a copy is right: the price paid

Normalized, a dish's price lives in the dishes table. But Ana paid $12 for her Margherita, and this week Forno Roma raises it to $13. If her receipt reads the price from dishes, it now says $13, and so do last month's sales and any refund. So each order item keeps its own price_paid, copied from the menu when the order is placed and never changed afterwards.

That doesn't break the rule: the copy is a different fact, what she paid then, not what the dish costs now. The address an order went to, picked from saved addresses, is the same: if Ana moves, her old orders were still delivered to the old one.

Denormalize on purpose

Sometimes you copy the same fact, for speed. The home screen shows 10 nearby restaurants with their star ratings. Say it's opened 50,000 times a second at busy hours, and each restaurant has about 1,000 reviews. Averaging the reviews on every view reads 10 × 1,000 = 10,000 rows per view, about 500 million a second; an index on the reviews' restaurant_id finds them faster, but still reads them all.

So the restaurant row keeps a rating and a review_count, and each new review updates both, without rereading the others:

before       rating 4.6, review_count 1,000
new review   5 stars
after        rating (4.6 × 1,000 + 5) ÷ 1,001 ≈ 4.6, review_count 1,001

Each view now reads 10 rows instead of 10,000. This is denormalization: a deliberate copy of something a hot read would otherwise rebuild with a join or a sum. It costs extra writes: say about 1 million reviews a day, about 10 a second with a day taken as 100,000 seconds, so 10 extra writes a second to save 500 million reads. And the copy can be stale: updated in the same transaction as the review it's always right, while a background job or an event a moment later lets the rating lag a little.

The same goes for a restaurant's name on home feed cards, shown 50,000 times a second: copy it into the cards and let a job update them after a rename. Facts off the hot path stay stored once: a dish's price on the menu page (the menu reads the dishes table itself) and a customer's email.

Data modeling in a system design interview

Data modeling is the framework's Data step, right after Scope and before the API. Four moves, in order:

1. List the entities.
2. Give each its key and main fields.
3. Link them: the id on the many side, a join table for many-to-many.
4. Name the copies you keep on purpose, why, and how they stay in sync.

customers(id, name, email)
restaurants(id, name, rating, review_count)
dishes(id, restaurant_id, name, price)
orders(id, restaurant_id, customer_id, address)
order_items(order_id, dish_id, qty, price_paid)

A link points to a key, so keys come before links. Interviewers listen hardest for the last move: a copy you chose and can defend.

Common interview questions on data modeling

What is normalization, and why do it?

Storing each fact once and pointing to it by id. A change is then one write and the data can't contradict itself, which avoids update, insertion and deletion anomalies.

When should you denormalize?

When a read is hot and rebuilding the value with a join or an aggregate is costly, like a rating on every home screen view. Keep a copy, update it on write or with a background job, and accept extra writes and some staleness.

How do you model a many-to-many relationship?

With a join table that has one row per pair, such as order_items with order_id and dish_id. The pair is its key, and it can hold facts of its own, like the quantity or the price paid.

Summary

• Every copy is one more place to change; a missed one is an update anomaly.
• Normalize: store each fact once and point to it by id.
• Entities get tables; attributes are their columns.
• One-to-many: the id goes on the many side. Many-to-many: a join table with facts of its own.
• A copy is right when it means something different, like the price paid.
• Denormalize on purpose for hot reads, and say how the copy stays in sync and what it costs.