<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 12"><meta name="Originator" content="Microsoft Word 12"><link href="file:///C:%5CDOCUME%7E1%5Ckswanton%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml" mce_href="file:///C:%5CDOCUME%7E1%5Ckswanton%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml" rel="File-List"><link href="file:///C:%5CDOCUME%7E1%5Ckswanton%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx" mce_href="file:///C:%5CDOCUME%7E1%5Ckswanton%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx" rel="themeData"><!--[if gte mso 9]>
Looking for opinions here. I'm in the initial stages of a database design
with a requirement for change auditing/logging. I'm basically analyzing four different options
and thought others here might be interested in sharing their own experiences, disasters, success
stories…wtfs.. etc etc.
Option 1: History Tables
For each table that needs to be audited, create another table that matches the schema to keep each version of the row as it changes over time.
Pros: Easy, Fast, same code that loads records from the primary tables can
be used to load historical data as the schema matches.
Cons: Potentially doubles the number of tables in the database, lots of data, history tables schema must be
kept in sync with primary tables
Option 2: Same tables, versioned
For each table that needs to be audited, add some type of version identifier. Maybe 'VersionNumber' or something, where the highest or 'NULL' is the 'Current' - This is not my idea, so don't flame me too much here. I think this is doomed to fail if attempted.
Pros: ahhhh… no doubling of tables? – same code to load records can be used
regardless if the record is an ‘Audit’ record, or the ‘current’ record, data schema automatically stays in sync (in comparison to option 1)
Cons: Queries become much more complex throughout the application, lots of
data.
Option 3: Change Rollup (Just the new data at the column level)
A history table with 5 columns, ChangedTable, ChangedColumn, UserName, DateChanged, NewValue
Pros: Single (or far less) tables to store history, less data
Cons: More complex to load a record in a specific point in history, slower
(assumed), Lose <meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 12"><meta name="Originator" content="Microsoft Word 12"><link href="file:///C:%5CDOCUME%7E1%5Ckswanton%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml" rel="File-List"><link href="file:///C:%5CDOCUME%7E1%5Ckswanton%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx" rel="themeData"><link href="file:///C:%5CDOCUME%7E1%5Ckswanton%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml" rel="colorSchemeMapping"><style>
</style>referential integrity with the data (however might not be a huge issue) </m:defjc></m:rmargin></m:lmargin></m:dispdef></m:smallfrac></m:brkbinsub></m:brkbin></m:mathfont></m:mathpr></w:word11kerningpairs></w:dontvertalignintxbx></w:dontbreakconstrainedforcedtables></w:dontvertaligncellwithsp></w:splitpgbreakandparamark></w:dontgrowautofit></w:useasianbreakrules></w:wraptextwithpunct></w:snaptogridincell></w:breakwrappedtables></w:compatibility></w:donotpromoteqf></w:validateagainstschemas></w:punctuationkerning></w:trackformatting></w:trackmoves></w:worddocument></xml>
Option 4: Store the entire record, in some type of serialized fashion (XML
Serialization)
Pros: Easy
Cons: Lots of data, if the object model changes over time can cause issues when
deserializing unless all of the historical data was updated to match upon a new
version being deployed.
<o:p> </o:p>
I know which option I'm leaning towards. I've done this on a smaller scale before. The current design is currently around 250 tables, but a portion of those don't need to be audited as they are lookup data or whatever. Just figured I'd see what others have experienced before moving forward.