Normalisation in database

Normalisation of database is always a world of pain for data administrators.To what level is the normalisation is important for proper maintenance of the dat开发者_JAVA百科abase. What could be the performance issues if the normalisation is done on the local and the remote database?

I would recommend that you do not only think about "performance issues".

Maintainability of a db is also a very big issue!

When you have to change one address in the db and you have to change entries in three or more tables (uniformically) than you have a maintainance problem. Also this can be a performance issue.

Normalization is critical to most database designs. Performance isn't measured only for select statements but for inserts updates and deletes as well. For instance suppose you have denomalized and have company name stored in several tables. Company name changes and now instead of one update affecting one record, you need 5 updates aftecting 1,500,000 records.

Alternatively, denormalized structure can be hard to query (query languages being built for normalized structures) . For instance suppose you decided to store the business address and home address for each customer in the customer table. Now suppose you want to find out the number of customers in CA. Now you have to query two fields to get this data and add them together. Suppose you now need to add a second business address (some businesses have mulitple locations), now you need to change your database structure and all the queries dependant on that structure.

Normalisation is only really painful if you're trying to do it after you've been using the database for a while!

Normalising systems with a large number of entities too far usually causes performance issues but rarely causes maintenance issues. Generally, the more normalised a database is, the easier it is to see what links to where through what.

As with most things, the actual level of normalisation required is largely specific to the application.

I wouldn't say that it's pain raither common sense.

When you noramlize something, you actually just see to it that you wont have redundant data, which should be quite obvious why that's bad.

Now, if you have no idea what Noramlization is, check this Wikipedia Entry.

When designing your data-structure you will soon see that a lot of things might repeate themselves and if so, you can always extract that and make an abstraction in your model. And hand-in-hand with that you get foreign keys, candidate keys, primary keys and indexes which will help you speed up searches and make it easier for all of us.

However, there are times when people tend to "over"-noramlzie, but that might not be so bad.

Power to the noramlization!

Normalization overview

First Normal Form (1NF)

First normal form (1NF) sets the very basic rules for an organized database: Eliminate duplicative columns from the same table. Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key). Second Normal Form (2NF)

Second normal form (2NF)

further addresses the concept of removing duplicative data: Meet all the requirements of the first normal form. Remove subsets of data that apply to multiple rows of a table and place them in separate tables. Create relationships between these new tables and their predecessors through the use of foreign keys. Third Normal Form (3NF)

Third normal form (3NF)

goes one large step further: Meet all the requirements of the second normal form. Remove columns that are not dependent upon the primary key. Fourth Normal Form (4NF)

Finally, fourth normal form (4NF)

has one additional requirement: Meet all the requirements of the third normal form. A relation is in 4NF if it has no multi-valued dependencies.

Quote taken from this article.

"I have heard that its not easy to normalise a database even upto a third normal form."

I think you have a hearing problem. One of being in the wrong auditorium.

But then again, if say the opposite, then that will be "just another thing you've heard someone say".

So I won't bother, except to encourage you to try and document yourself properly on the advantages of normalizing (such as, e.g. "redundancy-free databases", meaning there is no way for the database to contain contradictory data), and the alleged (mostly false) disadvantages (such as, e.g., "loss of performance"), and then do some critical thinking for yourself.

It's not like such good documentation is not available, the issue having been debated about for decades already.