Schema Design Patterns

The previous lessons gave rule after rule: find the dependencies, split the relation, put repetition back only by measurement. In practice, the same modeling situations recur again and again, and each has an established counterpart. This lesson’s question is: which situation translates into which schema structure, and which common structures are actually the problem themselves?

Relationship Cardinalities

How a relationship between two relations translates into a schema depends on cardinality.

A one-to-many relationship is built by putting the foreign key on the “many” side. A member has several loan records; the member_no column sits in the loan relation. The reverse direction — holding the loan number in the member relation — means squeezing a list into a single cell, and it breaks first normal form.

A one-to-one relationship is rare and usually rests on one of two reasons: separating a sparsely populated part of a row, or keeping columns whose access depends on a different authorization level apart. The foreign key is placed on one of the two sides and backed by a uniqueness constraint.

A many-to-many relationship cannot be expressed directly; a junction table is placed between the two. Its key is a composite key made up of the keys of both sides:

sqlite3 :memory: <<'SQL'
.headers on
.mode box
PRAGMA foreign_keys = ON;
CREATE TABLE book (isbn TEXT PRIMARY KEY NOT NULL, title TEXT NOT NULL);
CREATE TABLE author (author_no INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE book_author (
  isbn      TEXT    NOT NULL REFERENCES book (isbn),
  author_no INTEGER NOT NULL REFERENCES author (author_no),
  position  INTEGER NOT NULL,
  PRIMARY KEY (isbn, author_no)
);
INSERT INTO book VALUES ('975-01', 'Lost Time'), ('975-02', 'Sea Lighthouses');
INSERT INTO author VALUES (7, 'Elena Marsh'), (8, 'Kevin Ashford');
INSERT INTO book_author VALUES ('975-01', 7, 1), ('975-01', 8, 2), ('975-02', 8, 1);
SELECT k.title, COUNT(*) AS author_count FROM book k
JOIN book_author ky ON ky.isbn = k.isbn GROUP BY k.isbn ORDER BY k.isbn;
SQL

┌─────────────────┬──────────────┐
│      title      │ author_count │
├─────────────────┼──────────────┤
│ Lost Time       │ 2            │
│ Sea Lighthouses │ 1            │
└─────────────────┴──────────────┘

A junction table often carries more than a plain mapping. The position column here holds the order in which the authors appear on the cover — an attribute that belongs to the relationship itself and fits no other relation. The loan transaction is really the same kind of thing: it carries the checkout and return dates that belong to the relationship between a member and a copy.

Hierarchical Data

Book subjects form a tree: fiction under literature, historical fiction under fiction. The simplest counterpart is a relation referencing its own key — this is called an adjacency list. Walking the tree requires a recursive query:

sqlite3 :memory: <<'SQL'
.headers on
.mode box
PRAGMA foreign_keys = ON;
CREATE TABLE subject (
  subject_no     INTEGER PRIMARY KEY,
  name           TEXT    NOT NULL,
  parent_subject INTEGER REFERENCES subject (subject_no)
);
INSERT INTO subject VALUES (1, 'Literature', NULL), (2, 'Fiction', 1), (3, 'Poetry', 1),
                        (4, 'Historical Fiction', 2), (5, 'Science', NULL);

WITH RECURSIVE branch(subject_no, name, depth, path) AS (
  SELECT subject_no, name, 0, name FROM subject WHERE parent_subject IS NULL
  UNION ALL
  SELECT k.subject_no, k.name, d.depth + 1, d.path || ' / ' || k.name
  FROM subject k JOIN branch d ON k.parent_subject = d.subject_no
)
SELECT depth, path FROM branch ORDER BY path;
SQL

┌───────┬───────────────────────────────────────────┐
│ depth │                   path                    │
├───────┼───────────────────────────────────────────┤
│ 0     │ Literature                                │
│ 1     │ Literature / Fiction                      │
│ 2     │ Literature / Fiction / Historical Fiction │
│ 1     │ Literature / Poetry                       │
│ 0     │ Science                                   │
└───────┴───────────────────────────────────────────┘

The query starts at the root and unfolds the tree by adding one level at each step. This is the same idea as breadth-first search from the Data Structures course; the difference is that the queue is held inside the query engine.

An adjacency list is cheap to write and expensive to read: retrieving a node’s entire subtree takes as many steps as its depth. Three common counterparts exist. Path enumeration stores, in each row, the path from the root as a string; a subtree query reduces to a prefix match, but moving a node updates the entire subtree. Nested set gives each node a left and a right number; reads are very fast, but an insertion shifts almost the whole table. Closure table stores every ancestor–descendant pair as a separate row; reads and writes are balanced, at the cost of a larger row count. The choice follows the ratio of reads to writes.

Subtype and Time

The subtype situation arises from types of a shared parent concept that carry different attributes: library materials can be a book, a periodical, or an audio recording; they have common fields and separate ones. Three counterparts are used. Holding everything in a single relation — placing every type’s columns side by side — produces a large number of null values. A separate relation per type makes common queries harder. Keeping common attributes in a parent relation and separate attributes in a subtype relation per type is the balanced counterpart; the subtype relation’s primary key is also a foreign key to the parent relation.

The time dimension hides two separate requirements. The first is the interval over which a fact is valid: between which dates a membership fee held which amount. Its counterpart is adding a start and end date to the row and constraining the intervals not to overlap. The second is storing a row’s past states: every change writes a new version row, and the current row is marked with a flag or an empty end date. Mixing the two produces a schema that gets neither the history right nor the current state cheaply.

Anti-Patterns

Some structures appear to solve the problem while eliminating its constraints. The most common is the key-value table, which holds columns as rows:

sqlite3 :memory: <<'SQL'
.headers on
.mode box
.nullvalue (empty)
CREATE TABLE book_attribute (
  isbn      TEXT NOT NULL,
  attribute TEXT NOT NULL,
  value     TEXT NOT NULL,
  PRIMARY KEY (isbn, attribute)
);
INSERT INTO book_attribute VALUES
  ('975-01', 'title', 'Lost Time'), ('975-01', 'publication_year', '2019'),
  ('975-02', 'title', 'Sea Lighthouses'), ('975-02', 'publication_year', 'twenty twenty'),
  ('975-03', 'title', 'Silent Garden');

SELECT b.value AS title, y.value AS publication_year
FROM book_attribute b
LEFT JOIN book_attribute y ON y.isbn = b.isbn AND y.attribute = 'publication_year'
WHERE b.attribute = 'title'
ORDER BY b.isbn;
SQL

┌─────────────────┬──────────────────┐
│      title      │ publication_year │
├─────────────────┼──────────────────┤
│ Lost Time       │ 2019             │
│ Sea Lighthouses │ twenty twenty    │
│ Silent Garden   │ (empty)          │
└─────────────────┴──────────────────┘

Three losses show up in the same output. No type or domain constraint can be written for the publication year, because the value column is shared by every attribute — twenty twenty has been entered. Required-ness cannot be enforced; the third book has no year at all, and there is no place for a NOT NULL to prevent it. And reading two attributes requires joining the table with itself; as the number of attributes grows, so does the number of joins.

This structure has a narrow legitimate use: attributes that are genuinely unknown in advance, defined by the user, and carry no expected constraint. When it is chosen to avoid a schema change, every guarantee the database provides is given back.

Three anti-patterns appear more often. A polymorphic foreign key tries to reference several relations with a single column — the target relation’s name is held in a separate column, and referential integrity becomes unenforceable. A numbered column name — phone_1, phone_2 — is first normal form written out horizontally. A magic value places a placeholder such as -1 or '1900-01-01' instead of a null; every query must filter it out, and when the filter is forgotten it leaks into totals.

Summary

• A one-to-many relationship is built with a foreign key, a many-to-many relationship with a junction table that has a composite key; the junction table carries the relationship’s own attributes.
• For a hierarchy, an adjacency list is cheap to write and expensive to read; path enumeration, nested set, and closure table shift this balance in different directions.
• Subtypes are modeled by keeping common attributes in the parent relation and separate attributes in a subtype relation per type.
• Validity interval and version history are separate requirements; trying to serve both with a single structure breaks both.
• A key-value table loses type, domain, and required-ness constraints all at once and adds a join per attribute; its legitimate use is limited to attributes that are not known in advance.

Next Step

The normalization topic concludes here: how to build the schema, how to split it, and under which conditions to deliberately merge it back have all been established. The remaining question is what kind of workload this schema will run under. A system that records individual loan transactions and a system that produces annual loan statistics want the same data, but not the same access pattern. The next topic begins with this distinction.