Schema Design: One Fact, One Place

Concept. Schema design decides which tables hold which columns. One rule does most of the work: store each fact once, in one place.

Intuition. Put everything in one Franken-table (or wide table) and a user's email is kept with every listen. The day Mickey changes his email, you have to fix it in every row, and you may miss some, and make the rows inconsitent. Split the repeated facts into their own tables so each lives once. That split is normalization, and it explains why our Spotify schema has been three tables since day one.

Our query-equivalence reasoning depended on keys and constraints.

The problem: a Franken-table (or wide table) repeats itself

Left: a single wide table holding listen_id, name, email, title, artist, and rating. Mickey's email is copied on all three of his rows (highlighted amber), and the artist Taylor Swift is copied on row after row (highlighted amber). Right: three red cards naming the anomalies this repetition causes. Update: Mickey changes his email, so you must fix every Mickey row and will miss one. Insert: a brand-new song nobody has played has no listen row to live on. Delete: removing Mickey's last listen deletes his email too. Color key: amber marks a fact copied many times; red marks the three anomalies that repetition causes.

Figure 1. Repeating a fact across rows creates update, insert, and delete anomalies. An update to Mickey's email changes many rows and can leave different copies of the same fact. An insert cannot store a song fact until some listen row exists. A delete can erase a fact you meant to keep: delete Mickey's last remaining listen and his email disappears with it, because no other row stored it.

The fix: give each fact one home

Pull the repeated facts out into their own tables. A user's email lives once in Users. A song's artist lives once in Songs. Listens keeps only the events, and points at the other two by key.

Three tables. Users holds user_id, name, and email, with a note that Mickey's email lives here once. Songs holds song_id, title, artist, and genre, with a note that Taylor Swift lives here once. Listens at the bottom holds listen_id, user_id, song_id, and rating, and two arrows run from its user_id to Users and from its song_id to Songs, the foreign keys. Color key: green is the normalized design; the arrows are foreign keys that point at the one place each fact lives.

Figure 2. Normalization stores each fact once and connects tables with keys. Boyce-Codd Normal Form (BCNF) gives a formal definition of this goal. Updating Mickey's email changes one row in Users, and joins pull that value into query results. A foreign key, like `Listens.user_id`, points to the row that stores the user fact.

Splitting the table is exactly why you JOIN. A query recombines the facts you separated, so every join you wrote in Module 1 was putting a normalized schema back together to answer one question. Normalization and joins are two halves of one design: pull facts apart to store them safely, join them back to use them.

The trade-off: normalize first, denormalize only when forced

Normalized (the default)

  • Each fact is stored once.
  • No update, insert, or delete anomalies.
  • Cost: a JOIN to recombine facts at read time.

Denormalized (the exception)

  • Facts are copied back together.
  • Reads skip the join.
  • Cost: every write must update each copy, or the copies drift apart.

Start normalized. Denormalize only after measuring a read bottleneck. Module 5 copies data on purpose to serve millions of reads.

So the three-table Spotify schema was a design choice all along: one fact, one place, recombined by joins. Keep the rule in your head every time you sketch a table. If a column repeats the same value down the rows, it probably belongs in a table of its own.