Log meta-data to be part of entity tables or separate?

Question

Hello. For entities in my application, I am planning to log common meta-data like DateCreated, DateModified, IPAddress, etc. Does it make sense to add these columns in entity tables or is it better to have a single table for logging meta-data where each row has link back to the item that it corresponds to? Later for purpose of reporting and analysis, I can create desired views.

Answer 1

If you are wanting to keep only the latest information (as in the LAST time it was modified and by what IP address) then put it right in the table. If you are wanting something more like an audit log, where you can see all historical changes, then it should be in a separate table.

Answer 2

This is a classical question about whether you should normalize your database or not. I would say that you should normalize, and only de-normalize if you require it (performance etc).

Answer 3

I use a special query that adds all these common columns (I call them audit columns) to all tables (using a cursor going over the information schema), which makes it easy to apply them to new databases.

My columns are (SQL Server specific):

RowId UNIQUEIDENTIFIER NOT NULL DEFAULT (NEWID()),
Created DATETIME NOT NULL DEFAULT (GETDATE()),
Creator NVARCHAR(256) NOT NULL DEFAULT(SUSER_SNAME())
RowStamp TIMESTAMP NOT NULL

Now, in a fully normalised schema, I'd only need RowId, which would link to an Audit table containing the other rows. In fact, on reflection, I almost wish I had gone down this route - mainly because it makes the tables ugly (in fact I leave these columns out of database schema diagrams).

However, when dealing with very large data sets, you do get a performance boost from having this data within the table, and I haven't experienced any problems with this system to date.

Edit: Might as well post the code to add the audit columns:

DECLARE AuditCursor CURSOR FOR
  SELECT TABLE_SCHEMA, TABLE_NAME from INFORMATION_SCHEMA.TABLES
  WHERE TABLE_TYPE = 'BASE TABLE'
  AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
  AND TABLE_NAME NOT IN ('sysdiagrams')
  AND TABLE_NAME NOT LIKE 'dt_%'

  -- NB: you could change the above to only do it for certain tables

OPEN AuditCursor
  DECLARE @schema varchar(128), @table varchar(128)

  FETCH NEXT FROM AuditCursor INTO @schema, @table

  WHILE @@FETCH_STATUS  -1
  BEGIN

    PRINT '* Adding audit columns to [' + @schema + '].[' + @table + ']...'

    IF NOT EXISTS  (SELECT NULL
        FROM information_schema.columns
        WHERE table_schema = @schema
        AND table_name = @table
        AND column_name = 'Created')
    BEGIN
      DECLARE @sql_created varchar(max)
      SELECT  @sql_created = 'ALTER TABLE [' + @schema + '].[' + @table + '] ADD [Created] DATETIME NOT NULL CONSTRAINT [DF_' + @table + '_Created] DEFAULT (GETDATE())'

      EXEC  ( @sql_created )
      PRINT ' - Added Created'
    END
    ELSE
      PRINT ' - Created already exists, skipping'

    IF NOT EXISTS  (SELECT NULL
        FROM information_schema.columns
        WHERE table_schema = @schema
        AND table_name = @table
        AND column_name = 'Creator')
    BEGIN
      DECLARE @sql_creator varchar(max)
      SELECT  @sql_creator = 'ALTER TABLE [' + @schema + '].[' + @table + '] ADD [Creator] VARCHAR(256) NOT NULL CONSTRAINT [DF_' + @table + '_Creator] DEFAULT (SUSER_SNAME())'

      EXEC  ( @sql_creator )
      PRINT ' - Added Creator'
    END
    ELSE
      PRINT ' - Creator already exists, skipping'

    IF NOT EXISTS  (SELECT NULL
        FROM information_schema.columns
        WHERE table_schema = @schema
        AND table_name = @table
        AND column_name = 'RowId')
    BEGIN
      DECLARE @sql_rowid varchar(max)
      SELECT  @sql_rowid = 'ALTER TABLE [' + @schema + '].[' + @table + '] ADD [RowId] UNIQUEIDENTIFIER NOT NULL CONSTRAINT [DF_' + @table + '_RowId] DEFAULT (NEWID())'

      EXEC  ( @sql_rowid )
      PRINT ' - Added RowId'
    END
    ELSE
      PRINT ' - RowId already exists, skipping'

    IF NOT EXISTS  (SELECT NULL
        FROM information_schema.columns
        WHERE table_schema = @schema
        AND table_name = @table
        AND (column_name = 'RowStamp' OR data_type = 'timestamp'))
    BEGIN
      DECLARE @sql_rowstamp varchar(max)
      SELECT  @sql_rowstamp = 'ALTER TABLE [' + @schema + '].[' + @table + '] ADD [RowStamp] ROWVERSION NOT NULL'
      EXEC  ( @sql_rowstamp )
      PRINT ' - Added RowStamp'
    END
    ELSE
      PRINT ' - RowStamp or another timestamp already exists, skipping'

    -- basic tamper protection against non-SA users
    PRINT ' - setting DENY permission on audit columns'
    DECLARE @sql_deny VARCHAR(1000)
    SELECT  @sql_deny = 'DENY UPDATE ON [' + @schema + '].[' + @table + '] ([Created]) TO [public]'
      + 'DENY UPDATE ON [' + @schema + '].[' + @table + '] ([RowId]) TO [public]'
      + 'DENY UPDATE ON [' + @schema + '].[' + @table + '] ([Creator]) TO [public]'

    EXEC (@sql_deny)

    PRINT '* Completed processing [' + @schema + '].[' + @table + ']'
    FETCH NEXT FROM AuditCursor INTO @schema, @table

  END

CLOSE AuditCursor
DEALLOCATE AuditCursor
GO