Modelling the data

Part of the free System Design course on LogicWiz, in the chapter: APIs and data modelling. The interactive whiteboard exercises and simulations run in the app.

Week 4, Day 3: The question the schema could not answer

"The tables were beautiful. Then product asked for 'all orders for this rider today', and the query took eleven seconds."

Kabir, on his first schema

Start from what the screen shows

Data modelling sounds abstract, so begin with a screen. SwiftKart's order page shows: the customer's name, the items with prices, the total, the delivery address, the rider's name and the current status. Each noun you can underline is a candidate entity: a thing the system remembers, such as customer, order, order item, product, rider.

Now ask how they relate. Relationships shape the tables:

  • A customer has many orders; each order has one customer. (one-to-many)
  • An order has many items; each item points to one product. (one-to-many, twice)
  • A rider handles many orders over time; an order has one rider at a time.

In a relational database, each entity becomes a table. Each "has one" becomes a foreign key. For example, an orders row stores customer_id, a pointer to the customer. That small pointer is all the relationship needs.

Keys and indexes: how a lookup stays fast

Every table needs a primary key, a value that identifies exactly one row, such as order_id. Give it a stable, unique value that never changes. A phone number is a poor key (people change numbers); a generated id is a good one.

An index is a second, sorted structure that helps the database find rows without reading the whole table. Think of the index at the back of a textbook: finding "monsoon" there is far quicker than reading every page.

Kabir's eleven-second query was orders WHERE rider_id = 77 AND created_at >= today. With 50 million orders and no index on rider, the database read all 50 million rows to find a few dozen. Add an index on (rider_id, created_at) and the same query touches a few dozen rows and returns in a few milliseconds.

The trade-off: every index makes writes slower and uses more storage, because each insert must update it too. So do not index every column. Index the ones your queries filter and sort by. That leads to the main rule of this lesson.

Access patterns first

Here is the sentence to carry into interviews: model your data for the questions you must answer quickly. Write them down before you draw a table.

For SwiftKart, the list might be:

  1. Show one order by id.
  2. List one customer's orders, newest first.
  3. List a rider's orders today.
  4. Update the order status many times a minute.
  5. Show a rider's live location.

Each pattern suggests an access path. 1 and 2 want an index on order id and on (customer_id, created_at). 3 wants the rider index above. Pattern 5 is different. The rider's location changes every few seconds and is read by key. Nobody needs to join it with anything. Around 30,000 riders each send their location every 3 seconds. That is about 10,000 tiny writes a second, a very different workload from the orders.

💡 Tip: In an interview, say your top three access patterns out loud before choosing a database. Then your choice of database follows from those patterns, not from fashion.

SQL or NoSQL is a modelling choice

A SQL database (PostgreSQL, MySQL) keeps data in tables with relationships. It supports joins and gives strong transactions: "debit this, credit that, or neither". It is the right default when data is related, the queries vary and correctness matters. That describes SwiftKart's orders and payments.

NoSQL is a family of stores. The common idea: you shape data around the specific lookups you will do, and it spreads across machines more easily. Many give up joins. Many offer only limited transactions, for example only within the data stored under one key. Two kinds matter here:

  • A key-value store looks up a value by its key, like a giant dictionary.
  • A wide-column store groups rows by a key and is built for very high write rates.

Both work best for huge volumes of simple lookups by key with a very high write rate. That is a good match for rider locations: write a row every few seconds per rider, read it by rider id, keep little history.

So a realistic design uses both: orders in SQL, locations in a key-value store. Do not treat "SQL versus NoSQL" as a belief. Make one decision per access pattern. The usual mistakes run both ways. Some teams choose NoSQL "to scale", then need the joins and transactions they gave up. Others push 10,000 tiny location writes a second through a relational primary that also serves orders.

Denormalisation: copying on purpose

A clean relational model stores each fact once. The product's name lives only in the products table; order items hold only product_id. That is normalised, and it prevents contradictions.

But now the order page needs a join across orders, items and products for every view. At the flash-sale numbers from Week 2, that join would run tens of thousands of times a second. Denormalisation is deliberately storing a copy to avoid the join: put the product name and the price-at-purchase into each order item row.

The cost: updates must now touch several places, and the copies can drift apart. Note that some copying is not denormalisation at all. The price paid is a historical fact about the order. So it belongs on the order item even in a fully normalised model. Rule of thumb: copy data that is read often and changes rarely, and keep a single source of truth for everything else.

Copying a column is a trade-off, not a mistake

Meera learned in college that "a good model never stores the same fact twice". So copying a column felt like cheating. It is a trade-off, and trade-offs are exactly what interviewers are listening for. State both sides: reads get faster and simpler, writes get more work and a risk of inconsistency. Then name which side your numbers favour.

📌 Summary: List your access patterns first, and index only the queries that must be fast. Choose SQL or NoSQL per access pattern. Copy data on purpose only when it is read often and rarely changes.

What Meera learns next

The data has a shape and the API has a contract. But one database and one service still cannot carry SwiftKart's growth. Next module: sharing the work across machines, and what happens to the data when you do.

Check your understanding

  1. "All orders for rider 77 today" takes eleven seconds on a 50-million-row orders table with no index on rider. What is the right fix?
  2. Rider locations arrive at about 10,000 writes a second, are read only by rider id and are never joined. Orders need joins and transactions. Which design fits?
  3. Each order item row stores the product name and the price paid. A reviewer says, "That is duplication; normalise it." What is the best reply?
  4. What should be the primary key of SwiftKart's customers table?