P-6.1 The Relational Model

What this is and why it exists

Before any query syntax, the model itself is worth understanding. Data as tables. Rows identified by keys. Relationships expressed by referring to those keys.

What makes it powerful is not the tables. It is that the database enforces the rules, not the application. A defect in one program cannot leave the data in a state no program expects.

That guarantee is the whole reason to use a database rather than a folder of files.

The vocabulary

• Table — a set of rows sharing the same named, typed columns.
• Row — one record.
• Primary key — the column or columns that uniquely identify a row.
• Foreign key — a column referring to another table's key.
• Constraint — a rule the database itself refuses to break.
• Orphan — a row referring to something that no longer exists.
• Join table — a table existing only to connect two others.

The mental model

A table is a set of rows with the same named, typed columns, and the type is enforced when you write. That alone is more than most file formats promise. A comma-separated file will happily accept the word "tomorrow" in a date column and say nothing.

A primary key is what makes a row identifiable. The database refuses duplicates and refuses missing values there. Choosing it well is the first design decision you make, and it is one of the few that is expensive to change later.

Foreign keys are where the enforcement really shows. Referring to a key in another table lets the database refuse rows that point at nothing. That is what stops orphaned records accumulating quietly for years. An order refers to a customer deleted in 2023, and every report has been wrong since.

Relationships come in two shapes worth recognising on sight. One-to-many is handled by one side holding the other's key. Many-to-many cannot be, and needs a table of its own holding both keys. Getting this right at the start prevents most schema mistakes, and getting it wrong produces a table with columns called *tag1*, *tag2* and *tag3*.

So what does a database give you that a file does not? Four things. Concurrent access without corruption. Constraints it enforces itself. Recovery after a crash. And a query language you did not have to write. Each of those is months of work to reproduce, and reproduce badly.

What you should now be able to explain or do

Describe data as tables with typed columns and say what typing on write buys. Choose a primary key and say what the database refuses because of it. Use a foreign key and explain what orphaned rows are. Tell a one-to-many relationship from a many-to-many one, and know when a join table is required. Name the four things a database gives you over a file.

Check yourself