CODEWITHSUNDEEP
sqlsql-serversql-server-2005database-design
test
test

Reputation: 2639

Creating a table specifically for tracking change information to remove duplicated columns from tables

When creating tables, I have generally created them with a couple extra columns that track change times and the corresponding user:

CREATE TABLE dbo.Object
    (
    ObjectId        int         NOT NULL IDENTITY (1, 1),
    ObjectName      varchar(50) NULL    ,
    CreateTime      datetime    NOT NULL,
    CreateUserId    int         NOT NULL,
    ModifyTime      datetime    NULL    ,
    ModifyUserId    int         NULL    
    )  ON [PRIMARY]
GO

I have a new project now where if I continued with this structure I would have 6 additional columns on each table with this type of change tracking. A time column, user id column and a geography column. I'm now thinking that adding 6 columns to every table I want to do this on doesn't make sense. What I'm wondering is if the following structure would make more sense:

CREATE TABLE dbo.Object
    (
    ObjectId        int         NOT NULL IDENTITY (1, 1),
    ObjectName      varchar(50) NULL    ,
    CreateChangeId  int         NOT NULL,
    ModifyChangeId  int         NULL    
    )  ON [PRIMARY]
GO

-- foreign key relationships on CreateChangeId & ModifyChangeId

CREATE TABLE dbo.Change
    (
    ChangeId            int         NOT NULL IDENTITY (1, 1),
    ChangeTime          datetime    NOT NULL,
    ChangeUserId        int         NOT NULL,
    ChangeCoordinates   geography   NULL
    )  ON [PRIMARY]
GO

Can anyone offer some insight into this minor database design problem, such as common practices and functional designs?

Upvotes: 1

Views: 236

Answers (1)

keenthinker
keenthinker

Reputation: 7820

Where i work, we use the same construct as yours - every table has the following fields:

CreatedBy (int, not null, FK users table - user id)
CreationDate (datetime, not null)
ChangedBy (int, null, FK users table - user id)
ChangeDate (datetime, null)
  • Pro: easy to track and maintain; only one I/O operation (i'll come to that later)
  • Con: i can't think of any at the moment (well ok, sometimes we don't use the change fields ;-)

IMO the approach with the extra table has the problem, that you will have to reference somehow also the belonging table for every record (unless you only need the one direction Object to Tracking table). The approach also leads to more I/O database operations - for every insert or modify you will need to:

  • add entry to Table Object
  • add entry to Tracking Table and get the new Id
  • update Object Table entry with the Tracking Table Id

It would certainly make the application code that communicates with the DB a bit more complicated and error-prone.

Upvotes: 2

Related Questions