Relationships

Real data is connected: customers place orders, and orders contain products. A relational database stores each kind of thing in its own table and links the rows with keys. This page covers the three common kinds of relationship and how to tell which one you need.

Why split data into several tables

You could keep everything in one table, repeating the customer on every order:

This causes problems as soon as the data changes:

• When Anna changes her email address, every one of her orders has to be updated. Miss one and the table contradicts itself.
• A customer without an order can't be stored at all.
• A customer's last order can't be deleted without losing the customer too.

Splitting the data means each fact lives in exactly one place. Anna's email address is in one row of customers, and her orders refer to that row.

Foreign keys

A foreign key is a column that holds the primary key of a row in another table. Here, customer_id in orders points at id in customers:

customers

orders

Orders 1 and 3 belong to Anna, and order 2 belongs to Ben. A join brings the two tables back together when you ask a question that needs both.

The foreign key column should have the same type as the key it points to. The automatic id column is a bigint, so customer_id should be one too.

One-to-many

One row in a table relates to many rows in another: one customer has many orders, but each order has one customer. This is the most common relationship.

The foreign key goes on the "many" side, so customer_id goes on orders. It can't go on customers, because one cell can't hold a list of orders.

Many-to-many

Rows on both sides relate to many rows on the other: an order contains many products, and a product appears in many orders. Neither table can hold the foreign key, so you add a third table in between, often called a junction table:

order_items

Each row links one order to one product. The junction table is also the natural home for facts about the link itself, such as the quantity.

One-to-one

One row relates to at most one row in another table: a customer has one set of delivery preferences. This is a foreign key that is also unique.

It is the least common kind. Usually these columns can simply go on the main table, so use a second table only when the extra data is large, rarely used or optional for most rows.

Keeping each fact in one place

Arranging tables so that each fact is stored once is called normalization. To check a design, ask two questions:

• Is the same value repeated across rows? If a supplier's address appears on every product, it belongs in a suppliers table.
• Does a column hold a list? A tags column with "red, large, sale" is hard to filter. Use a separate table with one row per tag.

There is one common exception: a value that should stay as it was at a moment in time. unit_price in order_items above looks like a copy of the product's price, but it records what the customer actually paid. The product's price can change later without rewriting past orders.

Links between tables are not checked

Many database systems can enforce foreign keys, so that an order can't point at a customer who doesn't exist. Tunori's database tools don't set up this check. The link between orders and customers exists through the values in customer_id, and the database won't stop a row from pointing at nothing. For example, deleting a customer leaves their orders behind, still holding the old customer_id.

Your tables still work the same way. A few habits keep the links correct:

• Mark foreign keys as required when a row can't exist without its parent, like an order without a customer.
• Delete in the right order. Ask for a customer's orders to be removed or reassigned before the customer is deleted.
• Check now and then. Ask "are there any orders whose customer no longer exists?" The assistant can find them with a query.
• Index foreign keys. Joins are faster with an index on customer_id, and it isn't created for you.

Describing relationships to the assistant

Describe your data in plain words and the assistant works out the tables:

We sell products to customers. Each order has several products with a quantity and the price paid. We want to see what each customer has spent.

It proposes the tables first, so check that each one holds one kind of thing and that the foreign keys are on the right side. Give foreign key columns descriptions too, such as "The customer who placed this order". They tell every agent how the tables connect.