Database Normalisation

Now would probably be a good time to explain about normalisation, what it is, why it is important and how it can be utilised by you to make your database as efficient as possible. 

So, What Is Normalisation?

Normalisation is the removal of design weaknesses and duplicated data in order to minimize conflicting data and improve the eventual quality of the database contents. The idea is to increase data integrity by: 

• Removing duplicated data.
• Removing redundant data that is no longer used. 
• Removing fields that already can be calculated by a function.

And as a result of this, you will be able to INSERT, UPDATE, DELETE and maintain your data as time goes on. Sounds simple enough? Well there are actually many levels of normalisation which I will discuss below:

Why Do I Have To Normalise My Database?

Normalising your database is vital for the integrity of your data. If your database is full of duplicated and redundant data, chances are data will be updated in one place and not another therefore causing data loss and corruption. Which nobody wants! 

Typically as a developer - we naturally think of the 3rd Normal Form first as it generally makes the most logical sense, however it is very important to at least be aware of the other Normal Forms when Normalising an already existing database that has unfortunately been neglected. 

Types of Normalisation

• First Normal Form (1NF) – All tables are 2-Dimensional and there are no repeating groups.
• Second Normal Form (2NF) – 1NF plus each element of data is represented by a primary key (or unique identifier) that cannot be subdivided into smaller bits of data.
• Third Normal Form (3NF) - 2NF plus all tables contain no data other than that which describes the intent of the primary key – so any additional data is stored within separate tables. Usually we have no need to go beyond Third Normal Form (3NF).
• Boycee-Codd (BCNF) – A modified version of 3NF which prevents logical inconsistencies appearing in the data.

For a really good and detailed explanation of Normalisation - please visit this website. I would have come up with examples myself but I wouldn't be able to put it in words as well as this webpage does, so kudos!

http://www.sqa.org.uk/e-learning/MDBS01CD/page_26.htm