Database Normalization

Normalization is a set of design principles for organizing tables so the same piece of information isn't stored redundantly in multiple places. It sounds abstract, but the problems it prevents are very concrete - so let's start with a scenario that goes wrong before naming any rules.

A Motivating Example - The Giant Flat Table

Imagine, instead of StreamHub's actual separate users, movies, and reviews tables, someone had built a single giant flat reviews table that also stored the reviewing user's email and the movie's release year directly on every review row:

The problem: Sam's email address is now duplicated across every single review Sam has ever written. If Sam changes their email, updating it means finding and updating every one of those rows - potentially thousands of them for an active user - and if even one row gets missed, the data quietly drifts out of sync, with different rows disagreeing about Sam's own email address. Normalization exists specifically to prevent this kind of redundancy and the inconsistency it invites.

First Normal Form (1NF) - Atomic Columns

1NF requires that every column hold a single, atomic value - never a list crammed into one field. Consider a "bad" design where a movies table has a genre_names column storing something like 'Action,Sci-Fi,Thriller' as one comma-separated string:

This looks convenient at first, but it makes even simple questions hard - "find every Sci-Fi movie" now needs fragile text-matching against a string instead of a clean, direct comparison, and there's no good way to enforce that each value in the list is actually a valid genre. StreamHub's real design, covered in Primary & Foreign Keys, avoids this entirely with the movie_genres junction table - each row pairs one movie_id with one genre_id, so every column holds exactly one atomic value. That's a 1NF-compliant way to model a many-to-many relationship.

Second Normal Form (2NF) - Depend on the Whole Key

2NF applies to tables with a composite primary key (a key made of more than one column), and it requires that every non-key column depend on the whole key, not just part of it. This is most relevant for a table like movie_genres, whose primary key is the combination of movie_id and genre_id together. If that table had, say, a movie_title column, that would violate 2NF - the title depends only on movie_id, not on the full (movie_id, genre_id) pair, so it doesn't belong there at all; it belongs on movies where it already lives.

Third Normal Form (3NF) - Depend Only on the Key

3NF goes a step further: every non-key column must depend only on the primary key, not on some other non-key column. The flat-reviews-table example from the start of this lesson is exactly a 3NF violation - in that table, user_email doesn't depend on the review's own id at all, it depends on which user wrote the review. Whenever a column's value is really a fact about something else referenced in the row (the user, in this case) rather than a fact about the row itself, it's a sign that column belongs on a different table, related back by a foreign key instead of copied in directly.

How the Normal Forms Build on Each Other

Each form assumes the one before it already holds - a table can't meaningfully satisfy 3NF without first satisfying 2NF and 1NF. In practice, designing tables around real-world entities the way StreamHub does - a users table for user facts, a movies table for movie facts, a reviews table for the review itself - tends to land you in 3NF almost automatically, without having to consciously check each rule one at a time.

Denormalization - A Deliberate, Practical Tradeoff

None of this means duplication is always wrong. Denormalization is the deliberate choice to duplicate some data anyway, usually for performance reasons in read-heavy systems. A realistic example: adding a cached avg_rating column directly on movies, kept up to date by a trigger whenever a review is inserted or changed - the same idea comes back in more detail in Triggers. This trades a small amount of duplicated, derived data for the ability to read a movie's rating instantly without recalculating an average across potentially thousands of reviews on every single page load.

Normalization is a genuine tradeoff between data integrity and query simplicity or performance - not an absolute rule to chase to its extreme in every situation. Most applications, including StreamHub, benefit from being properly normalized by default, and then denormalizing specific, measured hot spots only once there's a real performance reason to.