The plan: model a store

Three pieces, one store

An e-commerce boils down to three concepts that relate to one another:

1. Products: the catalog. Each product has a name, price and stock.
2. Cart: a temporary collection of products the customer wants to buy, with the quantity of each one. It lives in memory, not in the database.
3. Orders: when the customer pays, the cart turns into a permanent order that gets recorded.

Orders with line items: an N:M relationship

An order does not contain a single product: it contains several, and each product can appear in many different orders. That is a many-to-many (N:M) relationship, and it cannot be represented with a single column.

The solution is a junction table (also called a bridge table or a table of items/lines). Each row of order_items links one order with one product and stores the quantity:

products         order_items              orders
--------         -----------              ------
id  name         order_id  product_id     id  date
 1  Keyboard         1          1          1  2026-01-10
 2  Mouse            1          2          2  2026-01-11

Order 1 has two line items: 2 keyboards and 1 mouse. To know what an order contains, you do a JOIN across the three tables.