Normalization in practice - when to split data

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:

1SELECT k.tytul, kat.nazwa
2FROM ksiazki k
3JOIN 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.