Database Design for Revisions?

by

I think the key question to ask here is ‘Who / What is going to be using the history’?

If it’s going to be mostly for reporting / human readable history, we’ve implemented this scheme in the past…

Create a table called ‘AuditTrail’ or something that has the following fields…

[ID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NULL,
[EventDate] [datetime] NOT NULL,
[TableName] [varchar](50) NOT NULL,
[RecordID] [varchar](20) NOT NULL,
[FieldName] [varchar](50) NULL,
[OldValue] [varchar](5000) NULL,
[NewValue] [varchar](5000) NULL

You can then add a ‘LastUpdatedByUserID’ column to all of your tables which should be set every time you do an update / insert on the table.

You can then add a trigger to every table to catch any insert / update that happens and creates an entry in this table for each field that’s changed. Because the table is also being supplied with the ‘LastUpdateByUserID’ for each update / insert, you can access this value in the trigger and use it when adding to the audit table.

We use the RecordID field to store the value of the key field of the table being updated. If it’s a combined key, we just do a string concatenation with a ‘~’ between the fields.

I’m sure this system may have drawbacks – for heavily updated databases the performance may be hit, but for my web-app, we get many more reads than writes and it seems to be performing pretty well. We even wrote a little VB.NET utility to automatically write the triggers based on the table definitions.

Just a thought!

More Related Contents:

  1. How to implement a many-to-many relationship in PostgreSQL?
  2. How to create multiple one to one’s
  3. What are best practices for multi-language database design? [closed]
  4. Strings as Primary Keys in SQL Database [closed]
  5. Are soft deletes a good idea? [duplicate]
  6. A beginner’s guide to SQL database design [closed]
  7. Physical vs. logical (hard vs. soft) delete of database record? [closed]
  8. Storing money in a decimal column – what precision and scale? [closed]
  9. single fixed table with multiple columns vs flexible abstract tables
  10. Primary key or Unique index?
  11. How to design a database for User Defined Fields?
  12. Composite Primary key vs additional “ID” column?
  13. Database EAV Pros/Cons and Alternatives
  14. Max length for client ip address [duplicate]
  15. Name database design notation you prefer and why?
  16. MySQL – how to front pad zip code with “0”?
  17. Use email address as primary key?
  18. Composite Primary Keys : Good or Bad?
  19. SQL ON DELETE CASCADE, Which Way Does the Deletion Occur?
  20. SQL: Using NULL values vs. default values
  21. What does ON [PRIMARY] mean?
  22. What is the best way to implement soft deletion?
  23. Relational Data Model for Double-Entry Accounting
  24. Temporal database design, with a twist (live vs draft rows)
  25. Historical / auditable database
  26. Single or multiple databases
  27. How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
  28. How can I schedule a job to run a SQL query daily?
  29. SQLite – How do you join tables from different databases?
  30. Does MS Access support “CASE WHEN” clause if connect with ODBC?

Leave a Comment