Relational Data: Tables, Keys & Relationships
Relational databases are the backbone of business software. Let's explore how they organise data into tables and connect them with keys.
How relational databases organise data
A relational database is like a collection of spreadsheets that link to each other.
Each spreadsheet (table) has column headers (schema) and rows of data (records). The clever part: tables can point to each other. A “Customers” spreadsheet and an “Orders” spreadsheet share a Customer ID — so you can instantly find all orders for a specific customer.
This linking between tables is what makes them “relational.”
Core concepts
Tables, rows, and columns
A table represents one type of entity — customers, orders, products, drivers.
| Term | Also Called | What It Is |
|---|---|---|
| Table | Relation, entity | A collection of related data with a fixed schema |
| Column | Attribute, field | A named property with a data type (text, number, date) |
| Row | Record, tuple | One instance of the entity (one customer, one order) |
| Schema | Table definition | The blueprint: column names, types, and constraints |
Tom’s example — Pacific Freight has a Drivers table:
| DriverID | Name | LicenceClass | HireDate |
|---|---|---|---|
| D001 | Sarah Park | Class 5 | 2023-06-15 |
| D002 | James Tui | Class 2 | 2024-01-10 |
Every row follows the same structure. Every column has a defined type. That’s the relational model.
Primary keys
A primary key uniquely identifies each row in a table. No two rows can have the same primary key, and it can never be null (empty).
- In the Drivers table, DriverID is the primary key
- In a Customers table, it might be CustomerID or an auto-generated number
Primary keys can be:
- Natural keys: A real-world value that’s already unique (email address, licence number)
- Surrogate keys: A system-generated value with no business meaning (auto-increment ID, GUID)
Foreign keys
A foreign key is a column in one table that references the primary key of another table. This creates a relationship between them.
Tom’s example: A Deliveries table links to the Drivers table:
| DeliveryID (PK) | Destination | Date | DriverID (FK) |
|---|---|---|---|
| DEL-501 | Auckland CBD | 2026-04-20 | D001 |
| DEL-502 | Hamilton | 2026-04-20 | D002 |
| DEL-503 | Wellington | 2026-04-21 | D001 |
DriverID in the Deliveries table is a foreign key — it points to DriverID in the Drivers table. This means:
- You can find which driver handled each delivery
- You can find all deliveries for a specific driver
- The database won’t let you assign a delivery to a driver that doesn’t exist (referential integrity)
Types of relationships
| Relationship | Example | How It Works |
|---|---|---|
| One-to-many | One driver → many deliveries | Most common. FK in the “many” table points to PK in the “one” table |
| Many-to-many | Many products → many orders | Needs a junction table (OrderItems) linking the two |
| One-to-one | One driver → one licence record | FK with a unique constraint. Used to split large tables |
Junction tables for many-to-many
When two entities have a many-to-many relationship (one order has many products, one product appears in many orders), you need a junction table (also called a bridge table or associative table).
Example: Products and Orders
| OrderItems (junction) | |
|---|---|
| OrderID (FK) | ProductID (FK) |
| ORD-101 | PROD-A |
| ORD-101 | PROD-B |
| ORD-102 | PROD-A |
The junction table has two foreign keys — one pointing to Orders, one pointing to Products.
Exam tip: key recognition
The exam tests your understanding of keys and relationships. Quick guide:
- “Uniquely identifies each row” → Primary key
- “References another table” → Foreign key
- “Prevents orphaned records” → Referential integrity (via foreign keys)
- “One customer has many orders” → One-to-many relationship
- “Students enrolled in courses, courses have many students” → Many-to-many (needs junction table)
Flashcards
Knowledge check
Tom's Deliveries table has a DriverID column that points to the Drivers table. What type of key is DriverID in the Deliveries table?
Aisha's food app needs to track which students have ordered which menu items. One student can order many items, and one item can be ordered by many students. What does Aisha need?
🎬 Video coming soon
Next up: Normalization: Why Duplicate Data is Bad — learn why relational databases split data across tables.