Hi all!

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.

I'm still a bit confused on the set-up. Can you give us a couple of sample rows from Tables A,B, and X, as well as a couple sample rows from your two options?

See this post: http://www.dbforums.com/showthread.p...=1#post6360778

Sorry for the late response, but thanks for the help.
We have already decided to do it the easy way, i.e. writing to a separate table the changes in the database.

Where the hell is the job security in that?