Question

Generic database design to store logs of every database change

Database, Database design

Hi there,

Can anyone help me out by providing a generic database design for having logs of all the changes to my master table so that I can review all the changes at a later point of time through my application.

Every response will be appreciated....

thanks,
Sudhakar

What you want to monitor and capture, as well as your RDBMS will deturmin how you create your table or tables. These tables are written from the SQL Server point of view. You may need to adjust these for your RDBMS.

If you wanted to capture schema changes then a table something like this would be a good starting point.
CREATE TABLE SchemaMonitorObject
(LogId int IDENTITY(1,1) PRIMARY KEY,
ObjectName sysname,
ChangeTypeId int, /*1 Create Table, 2, Add Column, 3, Drop Column, 4 Drop Table*/
DateLogged datetime,
EnteredBy sysname,
ObjectType char(1));
CREATE TABLE SchemaMonitorColumn
(LogID int PRIMARY KEY,
ColumnName sysname);

If you needed to capture data changes then a table somethign like this would be a good starting point.
CREATE TABLE LogDataChange
(LogId bigint IDENTITY(-10000000, 1) PRIMARY KEY,
object_id bigint,
TableKeyId INT, /*This would be the unique identifier for the record of the source table which is being modified, typically the primary key from that table.*/
OldValue sql_variant, /*Using a trigger to capture the change the old value from the deleted virtual table. This will give you all the prior values in the log table, and have the current value in the table. We use the sql_variant data type so that all data types can be stored in a single column.*/
DateChanged datetime,
ChangedBy sysname,
WasRecordDeleted bit /*If the record was deleted this should be set to 1 so that it's known that the record was deleted at this point.*/)