SQL and MySQL course Β· Module 9: Indexes and normalization
Normalization in practice - when to split data
In this lesson5
You already know the three normal forms. Now let us see how to apply them sensibly in a real Library - because normalization is not a spell to repeat mechanically, but the art of keeping order.
One fact - one place
The golden rule of normalization is: write each fact exactly once. The category name "Philosophy" should not lie copied into a hundred book scrolls - once is enough, in the kategorie table, and let the books point to it through kategoria_id.
The cost of repetition (redundancy)
If the category name were repeated in every book, changing "Philosophy" to "Ancient Philosophy" would require fixing hundreds of rows at once. Miss one - and the Library loses consistency: some scrolls say one thing, some another. This is an update anomaly, which normalization prevents.
A composite key and 2NF
Recall 2NF: it concerns tables with a composite key. In wypozyczenia the natural key could be the pair ksiazka_id + czytelnik_id. If we kept the book title there, it would depend only on ksiazka_id - that is, on part of the key. This breaks 2NF. The title belongs in ksiazki, and only the cross-reference stays in wypozyczenia.
Joining the split tables
After the split we recover the data with the JOIN command - it is JOIN that stitches the separated shelves back into one result:
SELECT k.tytul, kat.nazwa
FROM ksiazki k
JOIN kategorie kat ON k.kategoria_id = kat.id;Sensible moderation
We normalize to avoid repetition and anomalies - but we do not split forever. The first three normal forms are enough for the vast majority of projects. That is exactly how our biblioteka database is built: clean, consistent and without needless duplication.
Spotted a mistake in this lesson?
Check yourself
Answer the questions from this lesson. Pick an answer to see right away whether it is correct.
1. What does the second normal form (2NF) concern?
2. Why is storing the same information (e.g. a category name) in many rows risky?
Hands-on tasks in the game
- Click in order
Click the elements of the query joining books with categories in the correct order: