Yet another problem I have had to address recently is the issue of state recreation. We require the ability to go back and "audit" or review decisions that were made in the past. A common situation that comes up, is we as a company get into a pickle. To help prevent the situation from happening again we investigate the problem to get a better understanding of the cause. We trace back the root cause to a decision or a number of decisions that were made in the past. This may be decisions to change forecast, create purchase orders, release work orders, whatever. A very common one we've been seeing over the last couple years is the decision to create (or not create) purchase orders for raw material. We end up either running out of a certain material, or having way too much in stock. We can look back at the history of PO's that were released and determine that either too many or too little were released which resulted in the current situation. Inevitably, what we hear back from the purchasers is "I was just ordering based on the information the system was showing me". This is most likely true, but the problem is we have no way of recreating what the system was telling that person at the time they made the decision to understand what went wrong. We can see that today we are short, or will run out of said material at some date in the near future, but what we really need to see is what information the system was providing the decision-maker weeks or months in the past.
For the purchasing example, there are a core set of 2-3 screens that the purchasers look at when making their purchasing decisions. So to satisfy this requirement, we would want to provide the user the ability to bring up those screens for a certain set of criteria (eg. part # = 123456), then provide the ability to display the data as it looked at a specific user-provided date.
What makes this problem tricky, is we can't really nail down the requirement to one or even few specific scenarios. We want to be able to go back in time and review any decision that was based off any screen in our application.
I've solved this problem in the past (or seen it solved by others) in a number of different ways. In some applications I've developed in the past there were certain pieces of data that we needed to be able to retrieve their values as of a certain point in time. For example, in one application I developed there was a Parts table with a Price field. This would track only the current price of the part, which was not acceptable. What we did was remove the Price field from the Parts table and create a new table called PartPriceJournal, with the fields PartNumber, Price, BeginEffectiveDate, EndEffectiveDate. Whenever a change was made to a part's price, the current PartPriceJournal record would be update with an EndEffectiveDate and a new record would be created with a BeginEffectiveDate of today, and a Null EndEffectiveDate.
This works good if you only have a few pieces of data that you need to track in this fashion and can identify them up-front; which does not apply to my current problem. This solution would not be feasable in our scenario because we need to track ALL data over time. Creating a separate Journal table for every single field in our database is just not reasonable.
We could take this solution and apply it at the row level instead of the individual field level. So instead of creating separate Journal tables for every field we want to track we can just add a BeginEffectiveDate and EndEffectiveDate to each table. Then any time a change is made to any data in a row, the whole row is given an EndEffectiveDate and a new row added with the change. There are a number of problems with this solution.
1) I haven't performed any estimations yet, but my gut tells me that the database size will grow at an unreasonable rate. Every change to every piece of data will result in a new row being added. I would have to do a little research to get some estimates about how many updates and deletes we expect to occur (actual physical deletes would not be allowed under this architecture, instead the EndEffectiveDate would just be set resulting in a logical delete), but my first thought is that it will result in extremely large tables, with inefficient indexes, and eventually result in disk space concerns.
2) The existing primary keys will all have to be changed. Now that a given entity can have multiple records for the multiple versions of it over it's life, the primary key will have to be modified to include at least the BeginEffectiveDate.
3) When creating foreign key relationships to a table it could prove tricky to figure out how to setup the relationship. Do you link to just the PartNumber in the Parts table, or are you linking to the record for that PartNumber as of a specific date? I can see situations where you want to link to the most recent version of that Part record, and other situations where you want to link to a specific version of that Part record.
In the future I plan on revisiting this issue to come up with a more robust solution, possibly similar to the table-level journalling I described above (assuming I can solve the 3 problems listed above). For the time being we have decided that as long as we aren't "losing" any data, we can proceed with the implementation and come back to figure out a solution to this issue in the future. To that end we have decided to simply take a backup copy of our database nightly and archive that. Of course we have nightly backups of our database in place already, however, our backups are done via tape drive, and a finite rotating set of tapes. These backups are only stored for approx 1 month I believe. We would need something that we can archive indefinately (at least 1 year). We took the simplest solution we could think of, and are planning on simply having SQL Server 2005 spit out a backup of the database each night to a file share using an automated job. Every morning we will have somebody burn this file to DVD and archive it in a secure location.
If we need to examine the information the system was providing when a decision was made we can grab a copy of the database off the DVD from the decision-date, and restore to a server somewhere. Then install a copy of our web service somewhere and point it at the restored database. Then we can just point the UI to the new web service and pull up any screen we want with the data from the date selected. All this is possible via config file changes without requiring any recompiles. This allows us to solve it in the short-term, but we would definately like something that is more elegant and user-friendly than having to recreate a new database every time we want to look at historical data. We may also want to provide applications that analyze the data as it changed over time. For example, we have been approached about creating an application that analyzes the accuracy of the forecast by seeing how the forecast for a specific date or date range has been modified over the past year (in theory it should be updated to become more accurate as we get closer to the date range in question). In order to support this we would need access to the historical data of how the forecast has changed over time, without implementing one of these solutions all we have access to is the forecast as of today.
When we eventually revisit this issue and decide on a more permanent solution, we will at least have all the data available to populate into our new architecture should we decide it is worth the effort.
I'd love to hear from anybody that has had to tackle this issue before and what solutions you considered. What were the issues you identified with the solutions you looked at? Please leave a comment with any thoughts you guys out there may have.