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 wide table and a user's email rides along on every listen. The day Mickey changes his email, you have to fix it in every row, and you will miss one. Split the repeated facts into their own tables so each lives once. That split is normalization, and it is why the Spotify schema you have used since day one is three tables, not one.

The problem: one big 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 remove a fact when the last row carrying it goes.

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

Two panels. Left, green, Normalized: each fact once, no anomalies, recombine with a JOIN; the cost is a join at read time. Right, amber, Denormalized: copy facts back together for fast reads with no join, accepting the risk that the copies drift apart; used for read-heavy analytics at scale. Color key: green is the safe default, amber is the deliberate exception you take only when a measured read cost forces it.

Figure 3. Normalization avoids anomalies and requires joins during reads. Denormalization copies facts to reduce join cost and can create inconsistent copies. Start with a normalized schema and denormalize after measuring a read bottleneck. Module 5 uses intentional copying 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.