Tables, Keys and Relationships

A relational database is a set of tables that point at each other. That one idea — tables linked by keys — is what lets you ask "how much has each customer from Dhaka spent on electronics?" across data that lives in four different places. It is also why a database can refuse bad data: an order for a customer who does not exist simply cannot be saved.

This page opens the BitByte Shop database you will use for the rest of the tutorial and explains how it is built. If you have not created shop.db yet, the next page, Setup: Your Practice Database, does it in two minutes; reading this page first is fine.

What you will learn

• What tables, rows and columns are, and how they compare with a pandas DataFrame
• Primary keys, composite keys and foreign keys, and how the database enforces them
• One-to-one, one-to-many and many-to-many relationships, and what a junction table is
• How to read the shop's schema, and why customer details are not copied into every order (a first look at normalization)

Tables, rows and columns

A table holds one kind of thing: customers, products, orders. Each row is one of those things, and each column is one fact about it, with a fixed name and type. Here is the whole customers table:

SELECT * FROM customers ORDER BY customer_id;

+-------------+-----------------+---------------------+------------+------------+
| customer_id | name            | email               | city       | joined_on  |
+-------------+-----------------+---------------------+------------+------------+
|           1 | Nadia Rahman    | nadia@example.com   | Dhaka      | 2025-11-03 |
|           2 | Tanvir Ahmed    | tanvir@example.com  | Chattogram | 2025-12-14 |
|           3 | Farhana Akter   | farhana@example.com | Dhaka      | 2026-01-09 |
|           4 | Rafiq Islam     | rafiq@example.com   | Sylhet     | 2026-01-22 |
|           5 | Sadia Chowdhury | sadia@example.com   | NULL       | 2026-02-05 |
|           6 | Imran Hossain   | imran@example.com   | Khulna     | 2026-02-18 |
|           7 | Mitu Das        | mitu@example.com    | Dhaka      | 2026-03-30 |
|           8 | Karim Uddin     | karim@example.com   | Rajshahi   | 2026-04-11 |
+-------------+-----------------+---------------------+------------+------------+

Eight rows, five columns. Customer 5 has NULL in city: in SQL, NULL means "unknown / missing", not the text "NULL" and not an empty string. You will meet it often.

The same ideas go by several names, depending on who is talking:

A table looks like a DataFrame, but there are real differences. A table lives on disk and can be far larger than memory; every column has a declared type; the database enforces rules such as "never empty" or "must be unique"; and a table's rows have no built-in order — that is why every query here ends with ORDER BY.

Primary keys: one row, one identity

A primary key (PK) is the column, or set of columns, that identifies each row. It must be unique and never NULL, and in practice it should never change. In customers it is customer_id. Try to add a second customer number 1 and the database refuses:

INSERT INTO customers (customer_id, name, email, city, joined_on)
VALUES (1, 'Nadia Rahman', 'nadia2@example.com', 'Dhaka', '2026-07-01');

Error: UNIQUE constraint failed: customers.customer_id

Why a number and not the customer's name or email? Names repeat — Bangladesh has many Nadia Rahmans — and emails change. A meaningless integer (a surrogate key) never needs to change, so nothing that points at it ever breaks. The email is still protected by its own UNIQUE rule.

A key can also have several columns: a composite key. In order_items the key is (order_id, product_id): an order can contain many products and a product can appear in many orders, but each product appears once per order (buying two mice is quantity = 2, not two rows).

Foreign keys: links between tables

A foreign key (FK) is a column whose values must exist as a primary key in another table. orders.customer_id is a foreign key to customers.customer_id, so each order says which customer placed it, without copying the customer's details:

SELECT order_id, customer_id, order_date, status
FROM orders
WHERE customer_id = 1
ORDER BY order_id;

+----------+-------------+------------+-----------+
| order_id | customer_id | order_date | status    |
+----------+-------------+------------+-----------+
|        1 |           1 | 2026-01-12 | delivered |
|        3 |           1 | 2026-02-03 | delivered |
|       10 |           1 | 2026-04-19 | delivered |
+----------+-------------+------------+-----------+

(WHERE keeps only the matching rows; it gets its own page, Filtering Rows: WHERE, AND, OR and NOT.) Now the rule at work — an order for customer 42, who does not exist:

INSERT INTO orders (order_id, customer_id, order_date, status)
VALUES (99, 42, '2026-07-01', 'pending');

Error: FOREIGN KEY constraint failed

The same protection works the other way: you cannot delete customer 1 while orders still point at them.

DELETE FROM customers WHERE customer_id = 1;

Error: FOREIGN KEY constraint failed

This is called referential integrity: every reference points at something real. PostgreSQL and MySQL (with its default InnoDB engine) enforce foreign keys out of the box, and their messages are more specific:

-- PostgreSQL
INSERT INTO orders (order_id, customer_id, order_date, status)
VALUES (99, 42, '2026-07-01', 'pending');

Error: insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"

SQLite checks foreign keys only when switched on, separately for every connection, with PRAGMA foreign_keys = ON. The course helper sqlhelp.py does this for you; when you open a database yourself, you must do it too (see Common mistakes below).

Relationships

Keys create three kinds of relationship. Say them as "one X has how many Y?":

Check the data, not your assumption

Orders and payments look one-to-one: one order, one payment. The data says otherwise:

SELECT payment_id, order_id, amount, method, paid_on
FROM payments
WHERE order_id IN (5, 6, 13)
ORDER BY payment_id;

+------------+----------+--------+--------+------------+
| payment_id | order_id | amount | method | paid_on    |
+------------+----------+--------+--------+------------+
|          5 |        6 |   5000 | bkash  | 2026-03-08 |
|          6 |        6 |  10000 | card   | 2026-03-10 |
+------------+----------+--------+--------+------------+

Order 6 was paid in two parts, while order 5 (cancelled) and order 13 (pending) have no payment at all. So the relationship is one order to zero, one or many payments. This matters the moment you combine tables: assume one-to-one here and you count order 6 twice in a revenue figure or a training set (the page Joining Many Tables, and the Duplicate-Row Trap shows exactly this).

The junction table

order_items turns one many-to-many relationship into two one-to-many ones. Here is every sale of the Wireless Mouse (product 4):

SELECT order_id, product_id, quantity, unit_price
FROM order_items
WHERE product_id = 4
ORDER BY order_id;

+----------+------------+----------+------------+
| order_id | product_id | quantity | unit_price |
+----------+------------+----------+------------+
|        1 |          4 |        1 |        900 |
|        7 |          4 |        2 |        900 |
|        9 |          4 |        1 |        900 |
|       14 |          4 |        1 |        900 |
+----------+------------+----------+------------+

A junction table can carry facts about the pairing itself: quantity, and unit_price — the price at the time of sale. Order 9 bought the keyboard for 4050 although its list price is 4500. If order_items only pointed at products.price, every past order would silently change whenever a price changed. For ML this is the difference between a correct training set and one that leaks today's prices into last year's examples.

The shop schema

The schema is the design of the database: its tables, columns, types, keys and rules. Here is BitByte Shop's; each link is marked with its "1" side and its "many" side (the many side holds the foreign key):

customers (customer_id PK, name, email UNIQUE, city, joined_on)
    │ 1
    ▼ many
orders (order_id PK, customer_id FK, order_date, status)
    │ 1                                     │ 1
    ▼ many                                  ▼ many
order_items (order_id FK, product_id FK,   payments (payment_id PK, order_id FK,
             quantity, unit_price)                   amount, method, paid_on)
             PK = (order_id, product_id)
    ▲ many
    │ 1
products (product_id PK, name, category_id FK, price, stock)
    ▲ many
    │ 1
categories (category_id PK, name UNIQUE)

employees (employee_id PK, name, department, manager_id FK, salary, hired_on)
    manager_id points back into employees: each employee has 0 or 1 manager

You can also ask the database itself. In SQLite, PRAGMA table_info lists a table's columns and PRAGMA foreign_key_list its links (MySQL has DESCRIBE orders; PostgreSQL's psql has \d orders):

PRAGMA table_info(orders);
PRAGMA foreign_key_list(order_items);

+-----+-------------+-------------+---------+------------+----+
| cid | name        | type        | notnull | dflt_value | pk |
+-----+-------------+-------------+---------+------------+----+
|   0 | order_id    | INTEGER     |       0 | NULL       |  1 |
|   1 | customer_id | INTEGER     |       1 | NULL       |  0 |
|   2 | order_date  | DATE        |       1 | NULL       |  0 |
|   3 | status      | VARCHAR(20) |       1 | NULL       |  0 |
+-----+-------------+-------------+---------+------------+----+
+----+-----+----------+------------+------------+-----------+-----------+-------+
| id | seq | table    | from       | to         | on_update | on_delete | match |
+----+-----+----------+------------+------------+-----------+-----------+-------+
|  0 |   0 | products | product_id | product_id | NO ACTION | NO ACTION | NONE  |
|  1 |   0 | orders   | order_id   | order_id   | NO ACTION | NO ACTION | NONE  |
+----+-----+----------+------------+------------+-----------+-----------+-------+

In table_info, notnull = 1 means the column may not be NULL and pk marks the primary key. (order_id shows notnull = 0 because in SQLite an INTEGER PRIMARY KEY is the row's own id: it can never be NULL, and if you insert NULL SQLite picks the next number for you.) foreign_key_list shows that order_items.product_id points to products.product_id and order_items.order_id to orders.order_id.

A first look at normalization

Why not keep one big sheet, as many shops do in Excel, with the customer's details on every order line? Let's build that flat table for Nadia's three orders and see what happens when she changes her email:

CREATE TABLE orders_flat (
    order_id       INTEGER PRIMARY KEY,
    order_date     DATE,
    customer_name  VARCHAR(100),
    customer_email VARCHAR(100),
    customer_city  VARCHAR(50)
);
INSERT INTO orders_flat VALUES
    (1,  '2026-01-12', 'Nadia Rahman', 'nadia@example.com', 'Dhaka'),
    (3,  '2026-02-03', 'Nadia Rahman', 'nadia@example.com', 'Dhaka'),
    (10, '2026-04-19', 'Nadia Rahman', 'nadia@example.com', 'Dhaka');

-- Nadia's new email is changed on her latest order only
UPDATE orders_flat SET customer_email = 'nadia.r@example.com' WHERE order_id = 10;

SELECT order_id, customer_name, customer_email FROM orders_flat ORDER BY order_id;

+----------+---------------+---------------------+
| order_id | customer_name | customer_email      |
+----------+---------------+---------------------+
|        1 | Nadia Rahman  | nadia@example.com   |
|        3 | Nadia Rahman  | nadia@example.com   |
|       10 | Nadia Rahman  | nadia.r@example.com |
+----------+---------------+---------------------+

Which email is right? The table now contradicts itself — an update anomaly. A flat design has two more problems: you cannot record a customer who has not ordered yet (Karim, customer 8) without inventing a fake order (insert anomaly), and deleting someone's only order deletes everything you knew about them (delete anomaly).

The shop's real design stores each fact once: the email lives in one row of customers, and orders point at it with customer_id. One UPDATE of one row fixes it everywhere. Splitting data this way is called normalization; the advanced tutorial covers its formal rules (1NF, 2NF, 3NF). For now the rule of thumb is: if the same fact is typed in two places, it will eventually disagree with itself.

Flat tables are not evil. A training set for a model, or a table behind a dashboard, is often deliberately flat (denormalized) because that is what the model or chart needs. The trick is to build it from the normalized tables with a query, not to keep the flat copy as the source of truth.

Common mistakes

• Forgetting to switch on foreign keys in SQLite. A plain sqlite3.connect() does not check them, so orphan rows slip in silently:
  PythonCopy

  import sqlite3
  
  raw = sqlite3.connect("shop.db")          # no PRAGMA foreign_keys = ON
  raw.execute("INSERT INTO orders VALUES (99, 42, '2026-07-01', 'pending')")
  print("orphan orders:", raw.execute("SELECT COUNT(*) FROM orders WHERE customer_id = 42").fetchone()[0])
  raw.rollback()                            # undo it
  
  raw.execute("PRAGMA foreign_keys = ON")   # the fix: run this on every new connection
  try:
      raw.execute("INSERT INTO orders VALUES (99, 42, '2026-07-01', 'pending')")
  except sqlite3.IntegrityError as error:
      print("rejected:", error)
  raw.close()

  OutputCopy

  orphan orders: 1
  rejected: FOREIGN KEY constraint failed

• Using a name as the key. Two customers called "Nadia Rahman" become one person. Use an integer id as the primary key and keep natural values (email) as UNIQUE columns, as customers does.
• Assuming a relationship is one-to-one. Check before you combine tables. Orders with more than one payment (GROUP BY and HAVING are taught in Aggregates, GROUP BY and HAVING):
  SQLCopy

  SELECT order_id, COUNT(*) AS payments
  FROM payments
  GROUP BY order_id
  HAVING COUNT(*) > 1
  ORDER BY order_id;

  OutputCopy

  +----------+----------+
  | order_id | payments |
  +----------+----------+
  |        6 |        2 |
  +----------+----------+

• Storing a list in one column, like products = '1,4' on the order. You cannot index it, join it or stop someone typing '1,,x'. Use a junction table: one row per order and product, as order_items does.

Try it yourself

1. Easy: What is the primary key of products, and which of its columns is a foreign key, pointing where? Check your answer with a PRAGMA.
2. Medium: List the rows of order_items for order 4. Then look up the names of those products in products.
3. Hard: Create a one-to-one table customer_profiles (customer_id that is both the PK and an FK to customers, plus preferred_language). Add a profile for customer 1, then show that a profile for customer 42 and a second profile for customer 1 are both rejected.

Answers

1. product_id is the PK; category_id is an FK to categories.category_id:

PRAGMA foreign_key_list(products);

2. Order 4 contains products 2 and 8 (Hands-On Machine Learning and Laptop Stand):

SELECT order_id, product_id, quantity, unit_price
FROM order_items
WHERE order_id = 4
ORDER BY product_id;

SELECT product_id, name
FROM products
WHERE product_id IN (2, 8)
ORDER BY product_id;

3. Making customer_id both the primary key and a foreign key gives "at most one profile per customer, and only for real customers":

CREATE TABLE customer_profiles (
    customer_id        INTEGER PRIMARY KEY,
    preferred_language VARCHAR(20) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);
INSERT INTO customer_profiles VALUES (1, 'bn');

INSERT INTO customer_profiles VALUES (42, 'en');

Error: FOREIGN KEY constraint failed

INSERT INTO customer_profiles VALUES (1, 'en');

Error: UNIQUE constraint failed: customer_profiles.customer_id

Summary

• A table holds one kind of thing; rows are the things, columns their facts. Rows have no built-in order.
• A primary key identifies each row: unique, never NULL, ideally a meaningless integer. A composite key uses several columns, as in order_items.
• A foreign key must match a primary key elsewhere; the database rejects orphans and deletes that would create them. In SQLite, switch it on with PRAGMA foreign_keys = ON.
• One-to-many uses an FK on the many side; many-to-many uses a junction table; one-to-one uses an FK that is also unique. Check the data — orders to payments is one-to-many.
• Store each fact once (normalization) and build flat tables for models and dashboards from it with queries.

Next: Setup: Your Practice Database creates shop.db on your computer from shop.sql, so you can run every query on these pages yourself.