Quoting Jerry Schwartz <jerry@stripped>:
>> -----Original Message-----
>> From: Jacob Steinberger [mailto:trefalgar@stripped]
>> 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
