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 any further.

Also this is actually being done in SQL Server 2008 but I figure the same rules apply as if I was still using Access and I find the people here rock!

Thanks!

 

[boblarson]

Here's a super easy Audit Trail to put in place to track changes:

http://www.access-programmers.co.uk/forums/showthread.php?t=140387

 

[xenou]

Save more data in your history table / data warehouse (e.g., include the segment name).

Adding an "effective" date or "valid" date range to the segment names attached to the business code would also work but now you are talking about serious effort to redesign the database structure and queries - which would have to include date parameters for all joins that include segment name, and might involve junction tables or a sub tables to handle the relationship between the segment and its historical values involving time durations (or changing the keys in the main table so that Segment Name + Valid Date are able to uniquely identify a row, rather than just Segment Name). If the "underlying thing" (EF Industrial) is still the same, despite having a new name, you probably want to also be able to report EF Industrial and EF Industrial Canada as one.

 

[sschrupp]

Thanks for the responses.

Currently we are putting all 6 fields into history. What we're trying to do is avoid that, especially since that's not the only process that uses the business table and stores information. Also, it's not just the Segment field that might change. Any field besides the Business Code field might at some point change due to various business rules, legislation, whatever.

We were thinking that we might be able to increase performance a bit as well as save on some space if we could reduce 6 fields to 1 or 2 in the millions upon millions of records.

And you are correct, we would want to be able to report on the old versions. If the customer needs to see 3 years of history for Business Code SGA they would want to see all versions of SGA.

If we went with business code and version, or date like suggested, then we could knock 4 fields out of storage. Queries could then still grab just the business code to get all available versions, or business code restricted by date ranges.

I think that could work! Thanks for suggesting date instead of just a version number. Hadn't even crossed my mind.. lol.

 

[xenou]

Yes, I was thinking after posting this that a date is essentially a version number sequenced as a date value (with the added "plus" that it can be compared to calendar values).

Time-oriented databases is a topic unto itself. I discovered in some reading on this that SQL defines time units such as intervals. How great would it be to write a query:

SELECT BusinessSegment FROM Table1
WHERE BusinessSegment.ValidDate OVERLAPS 2011Q1;

Some day ...