Normalisation and normal forms (1NF, 2NF, 3NF) explained simply

In short: Normalisation splits tables so that each piece of information is stored only once; this avoids redundancy and errors when changing, inserting and deleting.

In more detail: A badly designed table stores the same data several times. If a value changes, it must be adjusted everywhere, otherwise contradictions (anomalies) arise. The normal forms are rules that prevent this. They are a classic in the IHK exam.

In Depth

Example of an unnormalised table

Order(orderno, date, customer, customercity, item1, price1, item2, price2)

Problems: several items in columns (how many?), customer data repeated in every order, prices duplicated. If the customer changes city, all rows must be changed.

First normal form (1NF)

Each cell contains exactly one value (atomic), no repeating groups (item1, item2), there is a primary key. → The item columns become several rows: OrderLine(orderno, lineno, item, price, quantity).

Second normal form (2NF)

Additionally: every non-key attribute depends on the whole key, not only on part of it. Concerns tables with a composite key (orderno + item): if price depends only on the item, it belongs in its own table Item(itemno, description, price).

Third normal form (3NF)

Additionally: no transitive dependencies: non-key attributes depend directly on the key, not via another non-key attribute. If customercity depends on customer and customer on orderno, customer data belongs in its own table Customer(customerno, name, city); Order refers to it via a foreign key. Mnemonic: “The attributes depend on the key, the whole key and nothing but the key.”

Result and practice

Customer(customerno, name, city)
Order(orderno, date, customerno→Customer)
Item(itemno, description, price)
OrderLine(orderno→Order, lineno, itemno→Item, quantity)

Advantages: no redundancy, consistent changes, less storage. Disadvantage: more joins. That is why sometimes deliberate denormalisation is used (performance, analyses, data warehouse). Further normal forms (BCNF, 4NF, 5NF) deal with special cases. Good data models arise with the ER model, which is then converted into tables.

See also: