A practical example of normalization in relational databases

The most current version as of .

After learning about the concept of primary keys as well as foreign keys in relation databases and how to use them in theory, its time to take that onto a more practical level.

In this article, we will cover a practical example of how to apply the rules of database normalization to a given database table, which is not normalized.

Here is our example table to start with:

KundenNr Name Vorname Betrieb Leitung ArtikelNr Art.beschreibung Auflage
1 Jürgens Ina Maier AG Lempel 2 Flyer 2000
2 Schmidt Tom Müller GmbH Breier 3 Karte 5000
3 Jäger Franz Maier AG Lempel 1, 2, 3 Karte, Flyer, Plakat 1000, 500, 3000
4 Olsen Ina Schulze AG Sommer 2 Flyer 500
5 Jürgens Paula Müller GmbH Breier 1 Karte 3000

Our goal is it to bring that table into the third form of normalization.

Let’s go ahead and get the job done.

Here is our customers table:

id last_name first_name company_id
1 Jürgens Ina 1
2 Schmidt Tom 2
3 Jäger Franz 1
4 Olsen Ina 3
5 Jürgens Paula 2

And our companies ceoss table:

id last_name
1 Lempel
2 Breier
3 Sommer

companies table goes here:

id name ceo_id
1 Maier AG 1
2 Müller GmbH 2
3 Schulze AG 3

Here is our products table:

id name
1 Karte
2 Flyer
3 Plakat

And the orders:

id customer_id
1 1
2 2
3 3
4 4
5 5

Here we go for our order_items:

id order_id product_id product_amount
1 1 2 2000
2 2 3 5000
3 3 1 1000
4 3 2 500
5 3 3 3000
6 4 2 500
7 5 1 3000

Conclusion

Feel free to play around with the original table and add data to get into the game of optimizing the way data is designed through different tables using primary and foreign keys.

— David Wolf