Notesdata-models🌱 seedling

Relational Model

Also: Relational Database · RDBMS · Relational Data Model

Data lives in relations (tables of rows), and queries select rows by value, never by following stored pointers. Because the database, not the program, chooses access paths, layouts and indexes can change without rewriting a single query.

Why it exists

Hierarchical (IMS) and network (CODASYL) databases made programs navigate fixed access paths chosen at design time. A question that didn't match an existing path meant new code and often a new layout. Separating the logical model from the physical one removed that coupling.

How it works

  • A table is a set of rows with the same named, typed columns; a row is identified by its key, not its position.
  • Relationships are values: a foreign key column holds the ID of a row elsewhere, and a join matches them at query time.
  • Many-to-one and many-to-many relationships are natural (a join table for the latter), which is what makes Normalization practical.
  • Queries are written in a Declarative Query Language (SQL); a query optimizer picks indexes, join order and algorithms from statistics.
  • The schema is declared and enforced on every write (schema-on-write, the opposite of Schema-on-Read).

Trade-offs

Gains Costs
any table relates to any other; ad-hoc queries Object-Relational Mismatch with application objects
optimizer adapts plans as data and indexes change reassembling a nested object takes joins
one fact, one place: no update anomalies deep or variable-length paths need recursive SQL

In practice

  • Postgres, MySQL, SQLite, Oracle, SQL Server dominate OLTP; most now also store JSON and some (Oracle, since 23ai) run graph queries via SQL/PGQ.
  • Successive challengers (key-value, document, column-family, graph, vector) have mostly ended up adding SQL or being absorbed into relational systems as features.

Pitfalls

  • "Relational means rigid." Adding a column is usually a metadata change; backfilling data is the expensive part, in any model.
  • Treating the database as a dumb store behind an ORM and losing the optimizer's help (see N+1 in Object-Relational Mismatch).

Recall

These cards are also in your review deck, scheduled with spaced repetition.