Our shop schema
What you'll learn
- find the right table among Kotomarket’s five:
users,products,orders,order_items,events - read an : determine a relationship’s (1:N, N:M, 1:1) from where the sits
- unfold a many-to-many relationship through a junction table with two FKs — like
order_itemswithorder_id+product_id - explain why an order’s contents can’t be stored as a string of ids right inside
orders
Today QUERY brings the entire index up onto the main table at once. Five glowing planes hang in the air, slowly rotating — a map of the world that survived. Beside them flickers the conservation log appended to the , with one line underlined: "Handing over five tables and the trace of a sixth. — K." You count the planes. Exactly five. Where’s the trace?
QUERY: Don’t get distracted by traces. First learn these five well enough to find your way with your eyes closed — the archive will show you the rest itself, when it judges you ready.
Kotomarket’s five tables
From here on we’ll be working with this database — a of an online shop from 2024. Hold it like a map of the store: who walked in, what’s on the shelves, what they bought, and what they did before buying.
| Table | What it holds |
|---|---|
users | buyers: id, name, city, and signup date |
products | storefront items: id, name, category, price, and stock |
orders | orders: id, user_id, date, status, and total |
order_items | order contents: which product, how many, and at what price |
events | behavioral events: views, add-to-carts, and purchases |
The links read like this: one user → many orders; one order → many order_items; one product → many order_items. If you get lost, open the schema on the right — it’s like the floor plan of the shop.
Let’s peek into the orders table: every row has a user_id — a reference to a buyer. That’s how one table links to another, and the map stops being five separate islands.
user_id:| id | user_id | status | total_amount |
|---|---|---|---|
| 1 | 18 | paid | 5980.00 |
| 2 | 10 | paid | 19750.00 |
| 3 | 26 | cancelled | 9560.00 |
| 4 | 26 | paid | 15530.00 |
| 5 | 8 | paid | 26710.00 |
| 6 | 37 | paid | 42110.00 |
How to read an ER diagram
The map QUERY unfolded is what the industry calls an (entity–relationship): rectangles are tables, the lines between them are foreign-key links. The most important thing on a line is its : how many rows on one side correspond to how many on the other.
- 1:N, one-to-many — the most common relationship. One buyer, many orders. The always sits on the "many" side:
user_idis stored inorders, not the other way around. That’s the rule for reading it: find the FK, and you’ve found the N side. - N:M, many-to-many — one order has many products, and one product appears in many orders. You can’t express such a relationship directly in a relational database: there’s nowhere to put the foreign key. You unfold it through a junction table — for us that’s
order_items. Each of its rows carries a pair of references,order_id+product_id, and turns a single N:M relationship into two 1:N ones. Bonus: the relationship itself gains attributes — the quantity and the price at the moment of purchase. - 1:1, one-to-one — rarer: heavy or private fields, for instance, get moved out into a separate table with the same key.
-- The N:M resolution in action: "order — product" pairs from order_items
SELECT order_id, product_id, quantity
FROM order_items
LIMIT 6;
Pitfall: storing an order’s contents right inside orders — as a string like '7,12,3' or an array of ids. You can’t protect such data with a , count it honestly, or join it to the storefront without contortions. When you see a many-to-many relationship, create a junction table with two foreign keys, like order_items.
Interview question
Interview question: how do you implement a many-to-many relationship in a relational database?
Strong answer: through a junction table with two foreign keys to the tables being linked — like order_items with order_id and product_id: it turns N:M into two 1:N relationships. It also holds attributes of the relationship itself — quantity, the price at the moment of purchase. The of such a table is either a composite of the two FKs or a surrogate id.