Modelling the data

We've picked PostgreSQL. Now we decide what goes in it: which tables, which columns, and how they point at each other. This is the data model, and it's one of the longest-lived decisions in any system. Code gets rewritten every year or two. Tables tend to outlive the people who designed them.

In this lesson we'll model Tiffin's core data, and along the way meet the handful of ideas that cover most real-world modelling: keys, relationships, and the question of when to copy data instead of pointing at it.

Find the nouns

A good first pass is to read the requirements and underline the nouns. For Tiffin they are: restaurant, menu category, menu item, customer, address, order, order item, payment, rider, delivery.

Most nouns become tables. Each row in a table is one of that thing: one restaurant, one order.

Then, for each pair, ask: how many of one go with how many of the other?

The rule for storing them is simple. One-to-many: put the "one"'s id on the "many" side. Many orders, so each order row holds its customer_id. Many-to-many: add a table in the middle whose rows each link one of each.

Keys

Every table gets a primary key: a column whose value is unique for every row and never changes. That's what other tables use to point at a row.

CREATE TABLE restaurants (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name        TEXT NOT NULL,
  address     TEXT NOT NULL,
  lat         DOUBLE PRECISION NOT NULL,
  lng         DOUBLE PRECISION NOT NULL,
  is_open     BOOLEAN NOT NULL DEFAULT false,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

Why a meaningless number, and not something "natural" like the restaurant's name or the customer's phone number? Because natural things change. Restaurants rename. Customers change phone numbers. If the phone number were the key, every order pointing at that customer would need updating. A generated id never needs to change.

A foreign key is a column that holds another table's primary key, and tells the database to enforce it:

CREATE TABLE menu_items (
  id             BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  restaurant_id  BIGINT NOT NULL REFERENCES restaurants(id),
  category_id    BIGINT NOT NULL REFERENCES menu_categories(id),
  name           TEXT NOT NULL,
  description    TEXT,
  price_paise    INTEGER NOT NULL CHECK (price_paise >= 0),
  is_available   BOOLEAN NOT NULL DEFAULT true,
  position       INTEGER NOT NULL DEFAULT 0
);

REFERENCES restaurants(id) means the database refuses to save a menu item for a restaurant that doesn't exist. NOT NULL and CHECK add more rules. These are constraints, and they're worth using generously. Your server code should validate too, but the database is the last line of defence: it catches the bug in a script someone ran by hand at midnight.

The order, and its items

The heart of Tiffin is the order.

CREATE TABLE orders (
  id                   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id          BIGINT NOT NULL REFERENCES customers(id),
  restaurant_id        BIGINT NOT NULL REFERENCES restaurants(id),
  status               TEXT NOT NULL CHECK (status IN
                         ('placed','accepted','rejected','ready',
                          'picked_up','delivered','cancelled')),
  delivery_address     TEXT NOT NULL,
  delivery_lat         DOUBLE PRECISION NOT NULL,
  delivery_lng         DOUBLE PRECISION NOT NULL,
  subtotal_paise       INTEGER NOT NULL,
  discount_paise       INTEGER NOT NULL DEFAULT 0,
  delivery_fee_paise   INTEGER NOT NULL,
  total_paise          INTEGER NOT NULL,
  created_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at           TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  order_id      BIGINT NOT NULL REFERENCES orders(id),
  menu_item_id  BIGINT NOT NULL REFERENCES menu_items(id),
  name          TEXT NOT NULL,
  price_paise   INTEGER NOT NULL,
  quantity      INTEGER NOT NULL CHECK (quantity > 0),
  PRIMARY KEY (order_id, menu_item_id)
);

Look closely at order_items. It points at the menu item with menu_item_id, but it also stores the dish's name and price_paise. And orders stores the delivery address as text, not just an address_id. Isn't that duplicating data?

Yes, on purpose.

Point at it, or copy it?

There are two ways to connect an order to the price of a dish:

1. Point at it. Store only menu_item_id, and look up the price from menu_items whenever you need it.
2. Copy it. Store the price on the order item at the moment the order was placed.

Pointing is the textbook default, called normalisation: every fact lives in exactly one place, so it can never disagree with itself. If a restaurant renames "Chicken biryani" to "Chicken dum biryani", every menu shows the new name instantly.

But think about what the order means. It's a record of what the customer bought, at what price, delivered where. If the restaurant raises the biryani from ₹280 to ₹320 next month, last month's receipt must still say ₹280. If the customer later edits their saved address, last month's order was still delivered to the old one.

So the question to ask about any piece of data is:

Should this show the value now, or the value at the time?

Menus show prices now, so they point. Orders, invoices, receipts and anything legal or financial record the value at the time, so they copy. This kind of copy isn't a shortcut. It's the correct model of what happened.

There's a second, different reason people copy data: speed. Storing restaurant_name on every order would let "my past orders" skip a join. That's denormalisation for performance, and it does have a cost: if the restaurant renames, you either update thousands of old rows or live with old names. For Tiffin version one we don't need it. A join on an indexed key over 20 rows takes well under a millisecond. Denormalise for speed only when a measured query is too slow.

The rest of the model

Here's the whole thing at a glance.

%% caption: Tiffin's main tables. Each arrow points from a table to one that holds its id. (Orders also point at restaurants, and order items at menu items.)
flowchart TD
  restaurants --> menu_categories --> menu_items
  customers --> customer_addresses
  customers --> orders:::hot --> order_items
  orders --> payments
  orders --> deliveries
  riders --> deliveries

A few more decisions worth calling out:

• payments is its own table, one row per attempt, with the provider's reference, amount and status. Payments fail and get retried, so one order can have several attempts, of which at most one succeeds. Mixing that into orders gets messy fast.
• deliveries is separate from orders. The order is the customer's and restaurant's business. The delivery is the rider's: who, when picked up, when dropped off. Different screens, different rules, and a rider could be reassigned without touching the order.
• status is a constrained text column. It's readable in queries and logs, and the CHECK stops typos. The server still enforces which status can follow which.
• Most tables get created_at, and anything that changes gets updated_at. You will always want to know when.
• Nothing is ever hard-deleted from orders. A cancelled order is a status, not a missing row. Finance, support and disputes all need it.

Check the model against the questions

A data model is only good if it answers the queries you need. Let's test it with two from the last lesson.

"Show restaurant 42's placed orders, oldest first."

SELECT id, total_paise, created_at
FROM orders
WHERE restaurant_id = 42 AND status = 'placed'
ORDER BY created_at;

"Monthly sales and commission per restaurant."

SELECT r.name,
       SUM(o.total_paise) AS sales_paise,
       SUM(o.total_paise) * 20 / 100 AS commission_paise  -- Tiffin keeps 20% of each order
FROM orders o
JOIN restaurants r ON r.id = o.restaurant_id
WHERE o.status = 'delivered'
  AND o.created_at >= '2026-09-01' AND o.created_at < '2026-10-01'
GROUP BY r.id, r.name;

Both are straightforward. Both will also get slow as orders grows, unless the database can find the right rows without reading all of them. That's the next lesson.

CREATE TABLE ratings (
  order_id    BIGINT PRIMARY KEY REFERENCES orders(id),
  stars       SMALLINT NOT NULL CHECK (stars BETWEEN 1 AND 5),
  comment     TEXT,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);