DB design problem

[DutchProgrammer]

Currently I am designing a new application that will use a relational database.
One of these tables, table X, has 2 FK's to PK's of 2 different tables (TABLE_A and TABLE_B), and a field VALUE.

Now the user needs to be able to the value of a variable that will be stored in table X.
Besides, he should be able to see the previous 500 changes in a pop-up window.

There are now 2 options to realize this:

1.Add a separate table 'Changes', with fields TIMESTAMP, OLD_VALUE, NEW_VALUE, VALUE_TABLE_A (or FK_1), VALUE_TABLE_B (or FK_2)In case of an update of field VALUE in table X, the field VALUE will be overwritten, and a new record with the old and new values will be added to table 'Changes'.

2.Add field DATE_BEGIN, DATE_END, AND PREVIOUS_ID to table X.
In case of an update of field VALUE in table X, the field 'DATE_END' will get the current date (i.e. record is set inactive), and a new record will be added to the table with the two same FK's as the changed record and DATE_BEGIN set to the current date.
When retrieving the current values, which will be done much more often than viewing the changes, one has to retrieve the records in which DATE_END has a null value. When viewing the changes, one has to get all active records (will be about 8, since the two refered tables include 4 and 2 records respectively). Next for these about 8 records, a backtracking SQL expression has to be written to query recursively on PREVOUS_ID. Then the 500 most recent changes are determined.

The advantage of the second method is that there is no unrelated table in the database, so the design is cleaner.
Moreover it should be possible to retrieve the state at some specific date in the past.

However, in this DB it is highly unprobable that it would ever be demanded to automatically retrieve a past state (instead of watching the pop-up window 'Changes'). Besides, searching for the 500 most recent changes seems to be complex.
Finally, it is very hard to implement the same functionality for new records in TABLE_A and TABLE_B, since possibly all references to these tables may have to be updated in case of small changes in TABLE_A and TABLE_B. So this method is inconsistent in dealing with changes in tables that do have fields that are refered to by other tables (TABLE_A, TABLE_B), and tables that don't (table X). If you update the records in TABLE_A or TABLE_B, the changes pop-up window is not able to show the correct data at the time of a specific change.

Which option would you prefer?
I would be very grateful if you could provide me with advise, since I am fairly unexperienced in DB design.

 

[gemma-the-husky]

this whole area is really complex - it hinges on two important concepts - normalization, and audit trail. if you consider OLD values of a given field you either need to

a) store the active value effective at the time with the data or
b) store a mechanism to retrieve the active value from a history table
c) decide you dont need the old value (eg an old telephone contact number)

now solution b) is the normalised way to do it, but solution a) is likely to be quicker in use. Note that you could also consider keeping the audit trail, but ALSO storing the value to save looking it up. You could also consider using option b) for certain data fields only, but not all.

All this depends on your enterprise rules etc, and how traceable your data needs to be, which is a decision you will know in your environment

You wouldn't keep 500 changes, though - if you elect to save a history table, you would save as many as you require. And you wouldn't really need to search thorugh 500 changes to find the one you want - you OUGHT to have a mechanism to find the one you want with a single read

ie if your history stores the effective date for each change (assuming no overlaps) then you just need to read (indexed read) the history item with the earliest effective date AFTER the date you are interested in. (a single operation)

 

[DutchProgrammer]

Thank you for your help!