Database design for audit logging [closed]

by

One method that is used by a few wiki platforms is to separate the identifying data and the content you’re auditing. It adds complexity, but you end up with an audit trail of complete records, not just listings of fields that were edited that you then have to mash up to give the user an idea of what the old record looked like.

So for example, if you had a table called Opportunities to track sales deals, you would actually create two separate tables:

Opportunities
Opportunities_Content (or something like that)

The Opportunities table would have information you’d use to uniquely identify the record and would house the primary key you’d reference for your foreign key relationships. The Opportunities_Content table would hold all the fields your users can change and for which you’d like to keep an audit trail. Each record in the Content table would include its own PK and the modified-by and modified-date data. The Opportunities table would include a reference to the current version as well as information on when the main record was originally created and by whom.

Here’s a simple example:

CREATE TABLE dbo.Page(  
    ID int PRIMARY KEY,  
    Name nvarchar(200) NOT NULL,  
    CreatedByName nvarchar(100) NOT NULL, 
    CurrentRevision int NOT NULL, 
    CreatedDateTime datetime NOT NULL

And the contents:

CREATE TABLE dbo.PageContent(
    PageID int NOT NULL,
    Revision int NOT NULL,
    Title nvarchar(200) NOT NULL,
    User nvarchar(100) NOT NULL,
    LastModified datetime NOT NULL,
    Comment nvarchar(300) NULL,
    Content nvarchar(max) NOT NULL,
    Description nvarchar(200) NULL

I would probably make the PK of the contents table a multi-column key from PageID and Revision provided Revision was an identity type. You would use the Revision column as the FK. You then pull the consolidated record by JOINing like this:

SELECT * FROM Page
JOIN PageContent ON CurrentRevision = Revision AND ID = PageID

There might be some errors up there…this is off the top of my head. It should give you an idea of an alternative pattern, though.

More Related Contents:

  1. How to design a product table for many kinds of product where each product has many parameters
  2. What is Normalisation (or Normalization)? [closed]
  3. Database design – articles, blog posts, photos, stories
  4. Schema for a multilanguage database
  5. Calendar Recurring/Repeating Events – Best Storage Method
  6. How to version control a record in a database [closed]
  7. designing database to hold different metadata information
  8. Same data from different entities in Database – Best Practice – Phone numbers example
  9. Defining multiple foreign keys in one table to many tables
  10. What are the lengths of Location Coordinates, latitude and longitude? [closed]
  11. Are nulls in a relational database okay? [closed]
  12. How should I implement this schema in MongoDB?
  13. Why use multiple columns as primary keys (composite primary key)
  14. Database schema which can support specialized properties
  15. How do you deal with polymorphism in a database?
  16. Nullable Foreign Key bad practice?
  17. Composite primary key or not?
  18. Hash Collision – what are the chances? [closed]
  19. How do you determine how far to normalize a database?
  20. Can we create multicolumn unique indexes on MS access databases?
  21. Should I have a dedicated primary key field?
  22. What is the best database schema to support values that are only appropriate to specific rows?
  23. Best design for a changelog / auditing database table? [closed]
  24. Should I use a single or multiple database setup for a multi-client application? [closed]
  25. Database Design Best Practices [closed]
  26. A column as primary key or two foreign keys as primary key
  27. How to Store Historical Data [closed]
  28. Bill of materials database model
  29. What are design patterns to support custom fields in an application?
  30. mongodb schema design for blogs

Leave a Comment