The store's data model

An online store in four tables

So far you have worked with standalone tables. In a real application data lives spread across several related tables to avoid repetition. We model a store with four:

customers   (id, name, city)
products    (id, name, price)
orders      (id, customer_id, date)
order_items (id, order_id, product_id, quantity)

Relationships (foreign keys)

A foreign key is a column that points to the id of another table. That's how rows are connected across tables:

   customers.id ──< orders.customer_id        (a customer has many orders)
      orders.id ──< order_items.order_id      (an order has many lines)
    products.id ──< order_items.product_id    (a product appears in many lines)

The symbol ──< reads "one to many": one customer can have many orders, but each order belongs to a single customer.

The order_items table is the one that links orders with products: each row is an order line ("3 units of product X within order Y"). That's why it stores quantity there and not in orders nor in products.

Why so many tables?

• The price lives only in products: if it changes, you change it in a single place.
• The customer's name lives only in customers: it isn't repeated in every order.
• Each concept is stored once and the other tables reference it by id.

This is normalization: each piece of data has a single place where it is the truth.