Page 1 of 1

To normalize or not

Posted: Thu Aug 29, 2002 7:47 pm
by Icy
I have been designing databases for my own use (small and large projects) for a few years. I have always done normalization while planning everything, but I have been wondering lately if it is really worth the performance hit (from the joins) to save space. Any Opinions?

Matt

Posted: Fri Aug 30, 2002 3:27 am
by mikeq
Normalisation is not just about saving space, it is also about easier updates, deletions, data integrity etc.

Have you been normalising without really knowing why you do it?

If designing a transaction database (an application with lots of data input by users) then normalise.

If designing a database for reporting (Data Warehouse, DataMart) then denormalise and aggregate the data(Star/Snowflake Schemas), this would normally be fed from a normalised database(s).

Posted: Fri Aug 30, 2002 6:49 am
by jason
Sometimes you want to denormalize a little for better performance. Once you have a properly designed database, going back and making some exceptions can help.

There are certain cases I won't do this:
  • Physical money
    Real transaction data
If it's not one of these options, I will denormalize the database where it will help performance. When denormalizing, make sure you have a handler to take care of any potential updates.

I particularly like denormalizing for static data. I keep records, and these records will change. One thing I will do is keep track of where this record started, which will never change. I could find this out by a few joins, searching by date, but it's much easier to simple grab from the table.