List:General Discussion« Previous MessageNext Message »
From:Jacob Steinberger Date:August 26 2010 2:19pm
Subject:RE: Complicated SQL Query
View as plain text  
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

Thread
Complicated SQL QueryJacob Steinberger26 Aug
  • Re: Complicated SQL QueryJacob Steinberger26 Aug
    • Re: Complicated SQL QueryJangita26 Aug
      • Re: Complicated SQL QueryClaudio Nanni26 Aug
  • RE: Complicated SQL QueryJerry Schwartz26 Aug
    • RE: Complicated SQL QueryJacob Steinberger26 Aug