Re: Normalization question

leebert (leebertarian_at_yahoo.com) writes:
> Old Data Model (key is ID + Year + Month) :
>
> ID Year Month Amount FlagA CodeA
> 0001 2002 01 $1000.00 Y ABCD
> 0001 2002 03 $1000.00 Y ABCD
> 0001 2002 02 $1000.00 Y ABCD
> 0001 2002 03 $1000.00 Y ABCD
> 0001 2002 04 $1100.00 Y DEFG
> 0001 2002 05 $1100.00 Y DEFG
> 0001 2002 06 $1100.00 Y ABCD

><keep adding new records>

Whether the rewrite is actually worth the pain is very difficult to say without knowing the details, but the old model is certainly the one that is easiest to both access and maintain. But if changes are rare, the old model uses up some disk space.

You could however simplify the new model if you dropp End_Dt, then you don't have to bother about overlaps.

Our database employs both these models for various tables. For instance instruments interests changes very rarely, and are not accessed frequently, so that tabls is instrument id, changedate and rate. On the other hand, for currency prices we maintain a table that has a value each day. There is a job which copies the value from the previous day to the next. This table is huge, but it is very easy to access; just a simple join.

-- 
Erland Sommarskog, SQL Server MVP
sommar_at_algonet.se
Books Online (updated!) for SQL 2000 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp