What a relation is: tables, rows, keys, and constraints

A team stores “user has many tags” as a comma-separated column. Six months later: “how many users have tag X?” — every row must be parsed. A relational design answers that question as a point lookup. The difference is the model you start with.

What a relation is

Edgar Codd’s 1970 paper defined the foundations of every SQL database since. The vocabulary is small:

• Relation — a set of tuples sharing the same shape. The table is the SQL implementation.
• Tuple — a single row; every tuple in a relation has the same attributes.
• Attribute — a column; each draws values from a domain (a type).
• Candidate key — a minimal subset of attributes that uniquely identifies every tuple. A table may have several candidate keys; one is designated the primary key.

Why constraints are the model’s enduring win

The relational model does not just store data — it refuses bad data. Constraints are declarative rules the engine checks before any insert, update, or delete:

• PRIMARY KEY — uniquely identifies each row; implies NOT NULL and UNIQUE.
• FOREIGN KEY — a column referencing a primary or unique key in another table; the engine refuses orphan rows.
• NOT NULL — this attribute must always have a value.
• UNIQUE — this column or column set has no duplicates across rows.
• CHECK — an arbitrary boolean expression evaluated on every write; e.g. CHECK (amount >= 0).

Without constraints you have a key-value store with SQL syntax. Application bugs can commit bad data. With constraints, the engine refuses on behalf of every caller — application code does not have to remember the rules.

The metaphor

A relational schema is a library catalogue. Each table is a drawer (books, authors, loans). Each row is a card with the same fields. A loan card has member-id and book-id pointing at other drawers (foreign keys). The librarian (the engine) refuses to file a loan card if the book-id does not exist. The system stays coherent without humans re-checking.

A practical scenario

Sven · Origin server wants a “favourites” feature for a marketplace. Otto · Origin database asks the shape question. Sven says “user has many favourites.” Otto reaches for the model: users, items, favourites table with (user_id, item_id) as PK plus two FKs. Three DDL lines and the feature is structurally correct — no duplicates, no orphans, queryable both ways. A JSON-array shortcut breaks the moment you ask “who favourited this item.”

Another team stores addresses as comma-separated text on the user row. Six months later marketing asks “how many users in Oregon?” — every text field must be parsed. A year later finance asks “sales by state” — same problem on every order. A relational design (addresses table with structured columns) is two extra schema lines and three orders of magnitude cheaper to query.