Relational thinking and schema design

Why this matters

Your Week 6 API kept equipment in a JavaScript array. It worked until you restarted the server, and then every record was gone. This week you replace that array with a real database, and today you design its shape. Changing a schema after it holds real data is far more expensive than thinking about it now.

By the end of the hour you can look at a messy real-world spreadsheet and say which tables it should become, which column identifies each row, and where the links between tables belong.

From an array in memory to rows on disk

A relational database stores data in tables. A table has a fixed set of named columns and any number of rows.

• A column is one named fact with one declared type — text, whole number, date. Every row has that column, and every value in it must be of that type.
• A row (also called a record) is one instance of what the table is about: one pump, one service visit.
• The schema is the whole design: which tables exist, their columns, and the rules values must obey.

The difference from a JavaScript array of objects is that the shape is declared and enforced. In an array, one object can have serialNumber and the next serial_number, and nothing complains until production. In a table, a column exists for every row or not at all, and a value violating a declared rule is refused at the door.

Entities and attributes

An entity is a kind of thing your system keeps facts about. An attribute is one fact about one of those things. Entities become tables; attributes become columns.

Finding them is a reading exercise. Write what the system is for in plain sentences, then pick the nouns you must store facts about: "A technician records a maintenance visit against a pump." Three nouns, three candidate entities.

Two tests separate a real attribute from a hidden entity.

1. Does the fact belong to exactly one instance? A pump's serial number belongs to that pump alone, so it is an attribute of equipment. The date a visit happened belongs to the visit — the pump has many visits, each with its own date.
2. Does the value repeat, identically, across many rows? If you type "Feed Pump 3, PMP-0003" onto every service line, the pump is its own entity and the service line should point at it instead of copying it.

That second test is the whole reason relational databases exist. Copied data drifts: someone fixes a typo in one row and not the other twelve, and the system now holds two contradictory answers to one question. Storing a fact once means one place to correct it.

Primary keys

A primary key is the column (or set of columns) whose value uniquely identifies a row in that table. No two rows may share it, and it may never be empty. Anything referring to that row — another table, a URL, an API response — refers to the primary key.

There are two kinds. A natural key is an existing attribute that happens to be unique, such as a serial number. A surrogate key is a meaningless number the database generates purely to be an identifier, conventionally called id.

Prefer a surrogate id. Natural keys turn out not to be as fixed as promised: serial numbers get re-stamped after a rebuild, emails change, and a supplier eventually ships two parts with the same label. When the key changes, every reference to it breaks. A surrogate key has no meaning, so nothing in the real world can force it to change.

That does not mean serial numbers go unguarded — you still declare that they must be unique. It means identity and uniqueness are two different jobs with two different declarations.

Foreign keys and one-to-many

A foreign key is a column in one table holding the primary key of a row in another table — how one row points at another. Crucially, the database checks it: store a foreign key value matching no row in the target table and the write is refused.

The commonest relationship is one-to-many: one equipment has many maintenance records, each record belongs to exactly one equipment. The rule for where the foreign key goes is mechanical and worth memorising:

The foreign key lives on the "many" side, pointing at the "one" side.

So maintenance_records gets an equipment_id column holding an equipment id. Equipment does not get a list of record ids — a column holds one value, not a list.

Cardinality is how many rows on each side may participate. You write it on your sketch as 1 → many, and you also note whether the link is optional: a maintenance record without equipment is nonsense and must be forbidden, while equipment with zero records is normal and must be allowed.

Before tomorrow: install PostgreSQL

You need it running tomorrow, and installs sometimes need a second attempt, so start now.

# macOS, with Homebrew
brew install postgresql@18
brew services start postgresql@18

# Debian or Ubuntu Linux
sudo apt install postgresql
sudo systemctl start postgresql

On Windows, use the interactive installer from postgresql.org/download/windows/. It runs the server as a service that starts automatically.

Then ask whether the server is actually accepting connections:

pg_isready

/tmp:5432 - accepting connections

If it says no response, the server is installed but not running — start the service with the command above. Tomorrow you connect to it properly.

Walkthrough: one spreadsheet, three tables

Here is the sheet a maintenance office keeps. One row per service visit.

Date       | Equipment   | Serial   | Installed  | Technician | Notes
2024-02-11 | Feed Pump 3 | PMP-0003 | 2021-06-14 | R. Cruz    | Replaced seal
2024-09-03 | Feed Pump 3 | PMP-0003 | 2021-06-14 | R. Cruz    | Checked shaft
2024-05-19 | Chiller 1   | CHL-0001 | 2020-03-02 | A. Diaz    | Coil clean

Apply the repeat test. Feed Pump 3 / PMP-0003 / 2021-06-14 appears identically twice, and so does R. Cruz. Those are entities hiding inside a flat sheet. Pull them out:

equipment           id, name, serial_number, status, installed_on
users               id, email, full_name
maintenance_records id, equipment_id →equipment.id, performed_by →users.id,
                    performed_on, hours_spent, notes

Now read the relationships back in words, which is how you check a design: one equipment has many maintenance records; one user performs many records; each record has exactly one equipment and one user. Both foreign keys sit on maintenance_records, the "many" side, exactly as the rule predicts.

Notice what changed: the installation date is stored once, on the equipment row. Entered wrong, there is one row to fix rather than one per visit.

How to use AI today

Today's mode is tutor. A tutor-style request asks the AI to explain the concept, give a small example, then let you attempt it yourself — never to hand you a finished schema. A schema you did not reason through is one you cannot defend later.

Your turn

Produce an entity relationship sketch for users, equipment, and maintenance_records. Paper genuinely is fine; a drawing tool is fine too.

1. Write one plain sentence describing what the system does. Underline every noun you must store facts about.
2. Draw a box per entity, named plural, lowercase, underscore-separated: maintenance_records, not MaintenanceRecord. Consistent naming saves you typos all week.
3. List the attributes inside each box, one per line, each with the kind of value it holds: text, whole number, decimal, date, true/false.
4. Mark the primary key of each box PK. Use a surrogate id for all three.
5. Draw a line from maintenance_records to equipment, and another to users. Label the record end many and the far end 1, and write the foreign key column name on the line.
6. Beside the relevant attribute, write the constraints you already want, in plain English: "serial_number must be unique", "status must be one of operational, maintenance, retired", "a record must have equipment", "equipment may have zero records".
7. Test the design against a real question: "which pumps have had no service since 2024?" Trace it with your finger. If you cannot get between boxes along a labelled line, a relationship is missing.

Common pitfalls

• Making the serial number the primary key. It looks unique and it is meaningful, which is exactly why it changes. Use a surrogate id; declare the serial number unique separately.
• Putting the foreign key on the wrong side. Equipment cannot hold a column of "all its record ids" — a column holds one value. The key goes on the many side.
• Copying a fact into two tables "to save a join". Two copies drift apart and neither stays trustworthy. Store it once.
• Designing tables to match your API's JSON. Different jobs. An API shape can be assembled from tables later; a badly split table cannot be un-split once it holds a million rows.

Verify it yourself

Open today's reference, the PostgreSQL tutorial, and read the sections on concepts and creating a new table.

1. Find its definition of a table and a row. Does it use record as a synonym for row? Note which term it prefers — documentation you read later will assume it.
2. This lesson claimed the database refuses a foreign key value with no matching row. Find where the tutorial covers foreign keys and confirm or challenge that claim in its own words.

Write both answers on the back of your sketch. Tomorrow you find out whether the design survives contact with a real database.