Keys and Table Design: Why Data Is Split Into Tables

Before you learn JOIN, you need to understand why the data is in several tables at all. Beginners often ask: why not keep everything in one big table, like a spreadsheet? The answer is the heart of database design, and it explains the shape of every database you will meet at work.

The one-big-table problem

Imagine a single sheet that records every order line with all the details copied in:

order | customer     | city      | product   | price | qty
1     | Asha Rao     | Bengaluru | Notebook  | 120   | 3
1     | Asha Rao     | Bengaluru | Pen pack  | 80    | 2
2     | Asha Rao     | Bengaluru | Backpack  | 1400  | 1
3     | Ben Carter   | London    | Laptop... | 1800  | 1

Asha's name and city are written again on every row. This causes three problems that database people give names to.

• Waste and mistakes. If Asha moves to Pune you must change every row she appears in. Miss one and the data now disagrees with itself.
• Cannot add partial facts. You cannot record a new product until someone orders it, or a new customer until she buys something, because every row needs an order.
• Lost facts on delete. If you delete the only order of a customer, you also lose the fact that she exists.

The cure is to store each fact once, in the table where it belongs. People call this process normalisation. You do not need the formal rules to design a good small database. One idea covers most of it: one table for each kind of thing.

One table for each kind of thing

In the shop there are four kinds of things: customers, products, orders and order lines. Each gets a table. Facts about a customer, such as name and city, live in customers once. Facts about a product, such as its name and price, live in products once.

customers: id, name, city, joined
products:  id, name, category, price
orders:    id, customer_id, order_date, status
order_items: id, order_id, product_id, quantity

Primary key: every row has its own identity

A primary key is a column, or a small group of columns, whose value is different on every row and is never NULL. Most tables use a column called id. In SQLite, INTEGER PRIMARY KEY makes the database fill the numbers for you. In MySQL the same idea is written AUTO_INCREMENT, in PostgreSQL GENERATED ... AS IDENTITY or SERIAL.

A good primary key is stable. Do not use a person's name or phone number, since these can change or repeat. A simple number that has no meaning outside the database is safest.

Foreign key: a link to another table

A foreign key is a column that holds the primary key value of a row in another table. In orders, the column customer_id holds the id of a customer. It means: this order belongs to that customer.

orders
id | customer_id | order_date | status
1  | 1           | 2024-06-02 | delivered     <- customer 1 is Asha Rao
2  | 1           | 2024-06-20 | delivered
3  | 2           | 2024-06-21 | delivered     <- customer 2 is Ben Carter

When foreign keys are declared and the database enforces them, it will refuse to insert an order for a customer id that does not exist, and it will refuse to delete a customer that still has orders. In SQLite you must turn this on with PRAGMA foreign_keys = ON; for each connection. MySQL (InnoDB) and PostgreSQL enforce them by default. This protects you from orphan rows, orders that point to nobody.

Relationships: one-to-many and many-to-many

A one-to-many relationship is the most common. One customer has many orders, but each order belongs to one customer. The foreign key goes on the “many” side: orders.customer_id.

A many-to-many relationship is where each side can have many of the other. An order can contain many products, and a product can be in many orders. A single column cannot hold that, so you add a third table in the middle, called a junction table (or bridge table). Here it is order_items. Each row says: this order has this product, this many times.

orders  <--  order_items  -->  products
(one)        (many rows)         (one)

order_items: order_id -> orders.id
             product_id -> products.id
             quantity

Reading it from the middle: each order_items row points to one order and one product. That is two foreign keys. This pattern appears in nearly every shop, booking, school and library system you will see.

How to read a database you have not seen before

When you start a new job, you will often get a database with dozens of tables and no guide. These habits help.

1. List the tables and the columns of each (your tool has a schema view, or in SQLite type .schema).
2. Find the primary key of each table, usually id.
3. Find the columns that end with _id. They are probably foreign keys. Note which table each points to.
4. Draw boxes and arrows on paper. One arrow from each foreign key to the table it references.
5. Run SELECT * FROM table LIMIT 5 on each table to see real examples.

Ten minutes of this gives you a map. Without it, joins become guesswork.

Naming habits that help

• Use one clear style. This course uses lower case with underscores: order_items, customer_id.
• Name foreign keys after what they point to: customer_id points to customers.id.
• Keep table names plural or singular, but be consistent. Many teams use plural.
• Avoid spaces and special characters in names. They force you to quote everything.

Your task

1. Draw the four tables of the shop database as boxes. Write the primary key in each and draw an arrow for each of the three foreign keys.
2. Run SELECT * FROM orders WHERE customer_id = 1; and write down which customer it is by looking in customers.
3. Think of a library: books, members and loans. Decide which table holds the foreign keys, and say whether the relationship between members and books is one-to-many or many-to-many.

How to check it worked

• You found three foreign keys: orders.customer_id, order_items.order_id and order_items.product_id.
• You can explain why order_items exists: orders and products are many-to-many.
• For the library, loans is the junction table with a member id and a book id.