Modelling your data

Entities, keys and relationships.

Data modelling is deciding what things your system stores and how they connect. A weak model makes every feature awkward and is expensive to fix later, because live data depends on it. A good one makes the common questions simple. The method is the same whether you use SQL or not: find the things, give each an identity, then describe how they relate.

An entity is a thing the business cares about: customer, restaurant, dish, order. Each entity becomes a table (or collection) with attributes as columns. Every row needs a primary key, a value that identifies it forever. Prefer an id that carries no meaning, like a number or a random id, over something real like an email address, since people change emails. A foreign key is a column that points at another table's primary key, such as customer_id on an order.

There are three relationship shapes. One-to-many: one customer has many orders; the order stores customer_id. Many-to-many: an order contains many dishes and a dish appears in many orders; you need a middle table, here order_items, holding both ids plus quantity and the price at that moment. One-to-one is rare and often just a column in disguise.

CREATE TABLE orders (
  id          BIGINT PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(id),
  status      TEXT NOT NULL,
  created_at  TIMESTAMP NOT NULL
);

CREATE TABLE order_items (
  order_id   BIGINT REFERENCES orders(id),
  dish_id    BIGINT REFERENCES dishes(id),
  quantity   INT NOT NULL,
  unit_price NUMERIC(10,2) NOT NULL,   -- copied: prices change later
  PRIMARY KEY (order_id, dish_id)
);

Normalization means storing each fact in exactly one place. If a dish name were copied into every order item, renaming it would mean updating thousands of rows, and missed rows would disagree. So you keep the name in dishes and refer to it by id. The rule of thumb: a normalized model prevents contradictions and is the right starting point. Denormalizing (copying data on purpose) can make reads faster, but it moves the burden of keeping copies consistent onto you, so do it only when measured.