What exactly does database normalization do?

database database-normalization
samul picture samul · Jul 9, 2009 · Viewed 10.7k times · Source

New to database and so no to get upset with simple questions. As far as my googled and gathered knowledge normalization reduces redundancy of data and increase the performance. But really, I didn't understand what exact reason for dividing the master table into other small tables, applying relationship among them, retrieving the data using all possible unions,subqueries,joins etc., Why can't we have all the data in a single table and retrieve them as on required. I'm little confused.

Answer

Steve Temple picture Steve Temple · Jul 9, 2009

The main reason is to eliminate repetition of data, so for example if you had a user with multiple addresses and you stored this information in a single table the user information would be duplicated along with each address entry. Normalisation would seperate the addresses into their own table and then link the two using keys. This way you wouldn't need to duplicate the user data, and your db structure becomes a little cleaner.

Full normalisation will generally not improve performance, in fact it can often make it worse but it will keep your data duplicate free. In fact in some special cases I've denormalised some specific data in order to get a performance increase.

Related questions

What are database normal forms and can you give examples?

In relational database design, there is a concept of database normalization or simply normalization, which is a process of organizing columns (attributes) and tables (relations) to reduce data redundancy and improve data integrity. (as written on Wikipedia). As most articles …

Read More
Difference between 3NF and BCNF in simple terms (must be able to explain to an 8-year old)

I have read the quote : data depends on the key [1NF], the whole key [2NF] and nothing but the key [3NF]. However, I am having trouble understanding 3.5NF or BCNF as it's called. Here is what I understand : BCNF is …

Read More
Is storing a delimited list in a database column really that bad?

Imagine a web form with a set of check boxes (any or all of them can be selected). I chose to save them in a comma separated list of values stored in one column of the database table. Now, I …

Read More