Hi Folks!

Merry Christmas, Happy Ramadan, Hunnukah, Kwaanza, whatever.....

Since the issue was raised again, a simple example of normalization and denormalization is as follows:

You have a telcom company. You have three tables (entities), one each for Customers (customer), Billing Rates (rate), and Call History. In the Call History table (callhist) you want to capture the customer, rate, length of call, and charges.

For the sake of the example, let's say that you are not tracking historical customer attributes but you need to lock in a rate at the time a callhist row is created.

What to do? In this situation, you want a normalized relationship between the customer and callhist because a name change or other change to a customers attributes is irrelevent. If Jane Smith becomes Jane Doe, she still made a certain call at a certain rate on a certain date.

However, rates may change but these changes should not be reflected in callhist. How would you like it if rate changes affected calls you made a year ago? In this case, a denormalized relationship is needed.

How do you implement this? By storing a foreign key in callhist pointing to the appropriate customer row, but by storing the actual rate and NOT the rate key in callhist.

Got it?