Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

How can I normalize a table that has four columns for logging the latest activity for a single record:

  1. Created On

  2. Created By

  3. Modified On

  4. Modified By

We have dozens of tables, all with these 4 columns. Is there a way or pattern to normalize this in an efficient and flexible manner?

share|improve this question
    
I was literally about to ask the exact same thing on here. –  Rudi Visser Dec 21 '12 at 15:10
1  
@RudiVisser Good we are on same boat i just pondered over this question too may be you it would help you stackoverflow.com/questions/237225/… –  Deeptechtons Dec 21 '12 at 15:13
    
Yeah this is the thing, I almost rationalised an external table for these 2 reasons: Ease of adding another "common logging" attribute, and that it's not going to be used very often apart from auditing. On the flipside, I'm still going to need to add a FK on each table to this log table, so not sure it's worth it. Eugh. –  Rudi Visser Dec 21 '12 at 15:19
    
@gbn could you try a hand on this –  Deeptechtons Dec 21 '12 at 15:21
    
Just curious, why do you need a foreign key here? Are you really worried that someone is going to bypass your process and write invalid data to your audit table? And do you really want to have to delete all of the auditing data when someone deletes a row in a primary table? That seems like exactly the kind of thing you want to keep... –  Aaron Bertrand Dec 21 '12 at 15:34

1 Answer 1

I would suggest not logging the "latest activity" but rather keeping a full audit trail. In order to minimize space requirements, you might want three tables:

CREATE TABLE dbo.Users
(
  UserID TINYINT IDENTITY(1,1) PRIMARY KEY, -- assuming <= 255 users
  Username NVARCHAR(128) NOT NULL UNIQUE,
  /* , other columns */
);

CREATE TABLE dbo.Tables
(
  TableID TINYINT IDENTITY(1,1) PRIMARY KEY, -- assuming <= 255 tables
  Name NVARCHAR(128) NOT NULL UNIQUE
  /* , other columns */
);

This auditing table assumes that all of the audited tables have an INT primary key (or a PK that will fit into INT). This will obviously be more complex if you have different data types, or compound primary keys, in which case you may just consider different auditing tables - still more valuable, IMHO, than only keeping the last modification for any one row.

CREATE TABLE dbo.AuditLog
(
  TableID       TINYINT NOT NULL 
                FOREIGN KEY REFERENCES dbo.Tables(TableID),
  ID            INT, -- loose reference to entity table's PK
  Action        CHAR(1) CHECK (Action IN ('I', 'U', 'D')),
  UserID        TINYINT NULL -- just in case
                FOREIGN KEY REFERENCES dbo.Users(UserID),
  EventDateTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

(Note that I intentionally did not dictate what the clustered index should be, since I don't know all of your query patterns aside from the one mentioned in the question. It is quite likely that you will want to cluster leading on EventDateTime, especially if you are going to query for events that happened recently, and/or you are going to purge data periodically, which is never a bad idea. This will at least ensure that new rows are added to the "end" of the table instead of page splits happening all over the place if you choose something like TableID as the leading column.)

Now your trigger can check the user name (not sure about your authentication method, but this may be via SUSER_SNAME()), it knows what table it's dealing with, it can determine the action, so it just has to add the ID(s) from inserted/deleted. Hokey example that assumes a table named dbo.foo with a primary key called FooID:

INSERT dbo.tables(Name) SELECT N'dbo.foo';
GO

CREATE TRIGGER dbo.AuditFoo
ON dbo.foo
FOR INSERT, UPDATE, DELETE
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @UserID TINYINT, @TableID TINYINT, @now DATETIME;

  SELECT @UserID = UserID -- could be NULL
    FROM dbo.Users WHERE Username = SUSER_SNAME();

  SELECT @TableID = TableID 
    FROM dbo.Tables WHERE Name = N'dbo.foo';

  -- inserts
  INSERT dbo.AuditLog(TableID, ID, Action, UserID)
  SELECT @TableID, FooID, 'I', @UserID
    FROM inserted AS i WHERE NOT EXISTS
    (SELECT 1 FROM deleted AS d WHERE d.FooID = i.FooID);

  -- updates
  INSERT dbo.AuditLog(TableID, ID, Action, UserID)
  SELECT @TableID, FooID, 'U', @UserID
    FROM inserted AS i WHERE EXISTS
    (SELECT 1 FROM deleted AS d WHERE d.FooID = i.FooID);

  -- deletes
  INSERT dbo.AuditLog(TableID, ID, Action, UserID)
  SELECT @TableID, FooID, 'D', @UserID
    FROM deleted AS d WHERE NOT EXISTS
    (SELECT 1 FROM inserted AS i WHERE i.FooID = d.FooID);
END
GO

Only one of those inserts will fire per statement / trigger invocation (well, to be pedantic, the trigger itself will fire multiple times in the event of MERGE).

If you don't want to keep a list of users / tables you can always populate this dynamically (though if you're not strictly controlling the list you may seriously reconsider the TINYINT suggestion above). E.g. to capture users you're seeing for the first time and haven't manually inventoried:

  SELECT @UserID = UserID FROM dbo.Users WHERE Username = SUSER_SNAME();

  IF @UserID IS NULL
  BEGIN
    INSERT dbo.Users(Username) SELECT SUSER_SNAME();
    SELECT @UserID = SCOPE_IDENTITY();
  END

But for the table this doesn't make much sense. Just add the row when you create the trigger, and you can even hard-code the TableID instead of deriving it at runtime.

Once you are collecting data in dbo.AuditLog, you should be able to easily derive information about the last action of any specific type, restricted to a table, user, or even individual entity. If you need help constructing any of those queries, please start a new question with the schema, some sample rows and desired / expected results. Using SQLFiddle can be quite helpful.

share|improve this answer
    
Good answer (+1). I've done much the same thing in the past with the addition of a varchar column to record some additional details about how the record was updated. You won't necessarily want or get full before/after, but you can get more information that lets you track down who did what and why. In one case I also included a column that indicated the source (part of the program) of the DB modification. This can be useful for records that can be modifed from multiple high-level business processes. –  Joel Brown Dec 21 '12 at 18:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.