Keep history of name changes

[sschrupp]

I have a table that lists a business code and then 5 fields of business information. Other tables will refer to that table using the business code to get business information. Once in a while one or more of those 5 fields will have a change.

When we make a change we only want it to apply to new records in our historical data, leaving older records so they maintain the old information.

So for instance one of our tables might track the date, a business code, and then a volume.

Business Codes
Bus_Code - L1 - L2 - L3 - L4 - Segment
SFA - EF - Core - HQ - PLEG - CF Aircraft
SGA - EF - Transportation - HQ - Construction - EF Industrial

Historical data
Date - Bus_Code - Volume
1/1/2001 - SGA - 12000
2/1/2001 - SGA - 13500

But lets say in 3/1/2001 we changed SGA so that the Segment name was "EF Industrial Canada". We would want to still know that Jan and Feb had the old "EF Industrial" and that going forward they had the new "EF Industrial Canada".

What would be the best way to keep track of that? One idea that came up was adding a version number to both tables. So then we would have:

Business Codes
Bus_Code - Version - L1 - L2 - L3 - L4 - Segment
SFA - 1 - EF - Core - HQ - PLEG - CF Aircraft
SGA - 1 - EF - Transportation - HQ - Construction - EF Industrial
SGA - 2 - EF - Transportation - HQ - Construction - EF Industrial Canada

Historical data
Date - Bus_Code - Version - Volume
1/1/2001 - SGA - 1 - 12000
2/1/2001 - SGA - 1 - 13500
3/1/2001 - SGA - 2 - 7700

Would that be the most efficient way to track this? Keeping in mind that the historical table has 30+ fields and around 4 million records a month.

The Business code table has maybe 400 codes so I don't know that it would help much to normalize that table any further.

Thanks!