To normalize or not

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Icy
Forum Newbie
Posts: 3
Joined: Tue Aug 20, 2002 11:13 am

To normalize or not

Post 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
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post 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).
jason
Site Admin
Posts: 1767
Joined: Thu Apr 18, 2002 3:14 pm
Location: Montreal, CA
Contact:

Post 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.
Post Reply