Here's my situation. A long time ago I built my users a customer application. They requested all changes be saved. So I built them a history module which is rather similar to one one described on this site: Audit Trail (See the customer/customer history section.)
Now my users are asking for the ability to add future changes to the systems based on their effective dates. Example: Customer Jane Dow will become Jane Smith on March 31 2008.
I don't really want to run a routine every night that checks for changes and updates the table accordingly. I want my data to be current when it's current, not when a script tells me it's current. (Unless I find out this is a better solution)
What I was thinking of doing was:
Have a table that stores the static Customer ID as that never changes. Then having a table joined (1-n) that contains all the customer data with an effective date (plus user and entry date to track who updated it and when). I call this table, FloatingCustomer as each time the Customer gets updated, a new instance is put into this table. When I retrieve the Customer data to be displayed, I will check the effective date and retrieve the one instance of the Floating Customer that is the most current to the current date. (Sorry for the overuse of the word "current").
Does that sound like a good way to go about it or is there something better? Any idea's or examples would be wonderful and much appreciated!
Thanks for your time.
P.S I don't think this will mater as I'm more looking for a design/concept but I'm using DB2 8 as the db to a Java Client/Server application.
I think your plan will work logically, but may suffer from performance problems.
99% of your queries will be for current data, but the engine will have to search through the entire set of historic and future data to get to it. Over and over again.
In the interest of efficiency, I would create a table to store future changes and roll them in nightly. Though this is an unusual user-requirement, it seems possible to implement without a lot of difficulty.
If it's not practically useful, then it's practically useless.
Thanks for your reply. I would love to do what you have suggested but my issue comes from what my users do with their customer data. They use the data to create reports based on dates. So if they need to reissue a report from last year, they need last years data. If the report is for next week, they need any known information that's been entered into the system to display on their report as it's for that period in time. It's not just for audit purposes...although I wish it was.
I'm starting to think that either way I go I'll have to look at "not current" data often. Hmmm...
Thanks for you help again. I'm still weighing my options.
Then, I would suggest that you enter "duplicate" records, with updated data, and an effectivity date. The table would have a primary key consisting of the customer ID and the effectivity date.
When using the data for reporting, you would select the max record with an effectivity date <= the target date.
"Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
"I have my standards. They may be low, but I have them!" - Bette Middler
"It's a book about a Spanish guy named Manual. You should read it." - Dilbert