Basic Database Normalisation Example

Basic Database Normalisation Example 

This is a basic example aimed at beginner level for those who are interested in normalisation. In a relational database our aim is to have minimal redundancy, this can be achieve through normalisation this usually means dividing the database in to several tables and then defining a relationship between then via a primary key and a foreign key. The idea behind normalisation is to have one point were data can be deleted or modified in one table, then this is will propagate through the database.

There are several normal forms but we will focus on the first 3.

• 1NF (First Normal Form) - Each column would contain different data, a example would be if we had a customer table and the table had the field order_date twice, in 1NF we would need to remove this field.
  
• 2NF (Second Normal Form) - Attempts to reduce the amount of redundant data in a table by extracting it, placing it in new table(s) and creating relationships between those tables.
  
• 3NF (Third Normal Form) - No duplicate information is permitted. So, for example, if two tables both require a birthdate field, the birthdate information would be separated into a separate table, and the two other tables would then access the birthdate information via an index field in the birthdate table. Any change to a birthdate would automatically be reflect in all tables that link to the birthdate table.

There are other levels of normalisation such as (BCNF - Boyce Codd Normal Form or 3.5NF), 4NF and 5NF, these alter normal forms can make the database more complex by dividing th data into more and more tables.

A final note about this process of normalisation, the more tables we have the more joins are needed the more joins the more expensive in terms of disk performance.