The Relational Model — Let One Table State One Fact

In a nutshell

The relational model represents data as tables of rows and columns, and by enforcing keys that uniquely identify rows and references between tables as constraints, it makes the data follow the rules by itself.

Why this was needed

Before the relational model, data was tied together by file structures or pointers, and the way to look things up was bound to the storage structure. If you changed the storage method, you had to fix every program.

The decisive idea of the relational model is to write down only what you want and let the database decide how to find it. This is why SQL is declarative. If we only write WHERE status = 'paid', the optimizer decides, based on the statistics at that moment, whether to use an index or scan everything.

How it works

Starting with terminology: a table is a relation, a row is a tuple, and a column is an attribute. Three kinds of keys attach to this.

• Primary key — uniquely identifies a row. It cannot be NULL.
• Candidate key — another unique attribute that could have been the primary key. Even if you use a surrogate key, if a natural key exists you must keep it as a unique constraint.
• Foreign key — references the key of another table. The database enforces referential integrity.

NULL is the most misunderstood part of the relational model. NULL is closer to "the value is unknown" than to "there is no value". So NULL = NULL is not true but unknown, and WHERE email = NULL returns no rows at all. This is why you must use IS NULL. This property spreads everywhere. count(*) counts rows, but count(email) counts only non-NULL values. If a NOT IN list contains even one NULL, all the results disappear.

It is also worth pointing out why constraints are placed in the database. If you keep a rule such as "this value is always greater than 0" only in the application, it breaks through three paths: manual SQL in production, batch scripts, and other services attached later. A constraint blocks all three.

What it looks like in the field

Choosing types is the hardest decision to reverse. In PostgreSQL, changing a column type is usually a rewrite of the whole table, during which a strong lock is held. An index, on the other hand, can be added and dropped at any time. So the reasonable order is to spend time on types and keys, and decide on indexes later by looking at the actual query patterns.

Two frequent mistakes are worth noting. If you use floating point for amounts, totals drift by one won. If you need exact calculations, use numeric. And if you write timestamp for a time column without thinking, it becomes a type without a time zone. For a value that points to a moment, you must use timestamptz, and since the storage size of the two types is the same 8 bytes, there is no reason to choose the type without a time zone to save space.

How far to normalize

The rules for splitting tables have names, but what you need to memorize in practice goes up to three levels, and those three actually reduce to one sentence. Each column holds only one value, every column must depend on the whole primary key, and non-key columns must not determine one another.

• First normal form — do not put several values in one cell, like "010-1111-2222, 010-3333-4444". If you store them this way, "finding the person who uses this number" becomes a string search and cannot use an index.
• Second normal form — if you keep the product name in a table whose primary key is (주문번호, 상품번호) (order number, product number), that value depends on only half of the key. The same product is stored repeatedly for every order, and if you miss one line when fixing the name, from then on the same product has two names.
• Third normal form — if an orders table has both 우편번호 (postal code) and 도시 (city), the city depends not on the order but on the postal code. When the postal code changes, the city must be fixed too, but that rule is written down nowhere.

What the three prevent is, in the end, one thing. When the same fact is written in several places, someday only one of them gets fixed, and from then on the database tells two contradictory stories at the same time.

That said, splitting as finely as possible is not always the right answer. The more you split, the more joins you get, and to draw a single list screen you end up joining five or six tables. So only when reads are overwhelmingly frequent and the value almost never changes do you deliberately leave duplication. Copying the product name and price at that time into the order is the typical example. This is not a normalization violation but actually accurate modeling, because the price at the time of the order is a different fact from the product's current price.

Conversely, if you copy a frequently changing value for performance reasons, you always pay a price. If you copy a member's grade into the orders table, every time the grade changes you have to decide whether past orders should change along with it, and that decision gets scattered across many places in the code. You can set the criterion like this: if the value is a "fact of now", reference it; if it is a "fact of then", copy it.

What you will do in the next lab

In a Pod running a real PostgreSQL 16, you will query an e-commerce schema. In particular, in the step of finding customers whose email is NULL, you will confirm by hand why = NULL does not work.