Quoting Jerry Schwartz <je...@gii.co.jp>:
-----Original Message-----
From: Jacob Steinberger [mailto:trefal...@realitybytes.net]
I have a requirement to keep track of a set of data, and all changes
that might occur. In order to do this, for each field of the data set,
I've created a table that keeps track of the version, the value, the
time the change was made, and a linking number that links all the
different tables back to a single record. I'm assuming, hoping, and
believe this is a very common setup.
[JS] That's a lot of tables. Do you need separate version tracking for each
individual field? Or would it be sufficient to have version tracking for each
row, with a list of fields modified and their "before" values?
If the latter, then you can get by with a lot less complexity; if the former,
then I think your design might be the only way to go.
Separate version tracking for each individual field. This is due to
all fields have a possibility of being edited/changed, but more than
likely only a few fields will be regularly updated.
The end idea that was given to me, which is quite easy, is to maintain
two methods. Use the multi-table method to track all the historical
changes, then use a single table with all the columns to base searches
/ processing off of. When needing to do an update, the only difference
is you update both locations instead of relying on a weird JOIN or
nested-sub-select view.
Cheers,
Jacob
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
