Relational Databases
Pedal rents bikes in three cities. Its first "database" was a spreadsheet: one row per rental, with the member's name and phone typed in again every time. When a member changed phone number, half the rows kept the old one. A relational database exists to stop exactly this kind of quiet damage.
The rule
Store each fact once, in a table; connect tables by keys.
A relational database keeps data in tables. Each table describes one kind of thing, each row is one instance of it, and each column is one property every row has. Tables point at each other through keys: a rental row does not copy the member's phone, it stores the member's id.
CREATE TABLE members (
member_id bigint PRIMARY KEY,
name text NOT NULL,
phone text
);
CREATE TABLE rentals (
rental_id bigint PRIMARY KEY,
member_id bigint REFERENCES members (member_id),
bike_id bigint,
started_at timestamptz NOT NULL
);Now a new phone number is one UPDATE to one row, and every rental sees it. A relational database management system (RDBMS) is the server that stores those tables and enforces the rules. PostgreSQL is one.
What an RDBMS gives you
- Integrity: keys, types and constraints refuse bad data at the door.
- Transactions: a group of changes happens completely or not at all.
- A shared language: SQL works across many products.
- Safe concurrency: hundreds of users can read and write at once.
What it costs you
- You design a schema up front, and changing it later takes planning.
- Joining many tables costs work on every query.
- One server grows mainly by getting bigger (more CPU, RAM, disk); spreading writes over many servers is hard.
The shortcut
When you meet any dataset, ask "What are the things, and which facts are repeated?" Each thing becomes a table. Each repeated fact moves into the table it belongs to and is referenced by key.
List the things
Rows mention members, bikes and rentals: three kinds of thing, so three tables.
The trap is thinking "relational" means "has relationships between tables". It comes from the mathematical word relation, which simply means a table of rows. The keys are how relations connect.
Drills
Answer fast — trust the rule.
In the Pedal design, where should a member's phone number live?
What does the REFERENCES members (member_id) clause on rentals do?
Which is a genuine limitation of a classic RDBMS?
A spreadsheet stores bike_model and bike_colour on every rental. What does the shortcut tell you to do?
Remember
- Store each fact once, in a table; connect tables by keys.
- Ask "what are the things, and which facts repeat?" and move each fact home.
- An RDBMS buys integrity and transactions at the price of up-front design and harder scale-out.