databasedatabase-normalization

What exactly does database normalization do?


Supposedly normalization reduces redundancy of data and increases performance. What is the reason for dividing the master table into other small tables, applying relationships between 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 it as required?


Solution

  • 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 separate 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.