new to DB design: what is the best way to design a LOG table

[alfredjp]

hi!

im new to database design, and i would like to know the best way to design a log table.

as of now, my present design is as below:

- Log_ID ---> primary key
- Log_Date
- Log_User_ID
- Log_Type
- Log_Procedure_Name

where "Log_ID" is an automatically generated integer.

a programmer in the team suggested the Log_ID is NOT needed because it DOES NOT have any actual use - the log table being but only a history record. any ideas on the matter?

also, if someone else has a better design, could you at least share it?

thanks!

 

[SemperFiDownUnda]

you are going to explicitly populate this table?

For logging I like a simple design where the table has a last modified and modified by column. Then have a log table with the exact same structure so something like

CREATE TABLE Customer
(
CustomerID INT, -- Primary Key
LastName VARCHAR(30),
.
.
.
ModifiedOn DATETIME,
ModifiedBy INT --FK to some user table system users
)

CREATE TABLE Customer_History
(
CustomerID INT, -- Primary Key
LastName VARCHAR(30) NULL,
.
.
.
ModifiedOn DATETIME,
ModifiedBy INT --FK to some user table system users
)

Then in the UPDATE trigger just
INSERT INTO Customer_History
(CustomerID,...)
SELECT CustomerID,..., GETDATE(), ModifiedBY FROM INSERTED

This is simplistic assumes you never actually DELETE records via your app but just mark them DELETED. They can be archived off later by higher level administrator types.

This also allows you to put back a record to any of its states in many cases. It is a hog on space and overkill in certian situations. It all depends on WHY you are actually logging changes. Logging them to show the user that things didn't misteriously change is perception problem that can be tackling it in other ways. This method only stop delibrate sabotage if the users that modify data don't have direct access to the database but only have access to middle tier business logic that actually makes the change.

Anyway that is a small pinhole view of how I like to do things.

 

[alfredjp]

thanks a lot...

how about other subscribers out there?

i know the best learning experience is that what is learned through experience

 

[CajunCenturion]

IMHO, all tables should have a primary key. I know that it's not absolutely necessary, but I think it's a good practice.

Somewhere down the road, you might the need to add more info to the history log. You might need to add a subordinate table for certain types of log entries. At that point, you will need the key. It's a log easier and cheaper to add the key now at the start, that it would be to add the key in later.

With it being a good practice, and facilitates expansion of the log down the road, I would be in favor of the Log_ID.



Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[SemperFiDownUnda]

The actual Primary key on my history tables tend to be the original primary key and the modifiedOn column.

And as I look at it I've informed you a bit wrong on what I do.

ModifiedOn is not filled in on the trigger but rather in the UPDATE StoredProcedure. The trigger actually just pushes the raw row to the history table.

Since this is done by the Stored proc if a DBA goes to modify a row directly they need to remember to update not only the columns they are playing with but the ModifiedOn Column too because if they don't all subsequent UPDATEs to the row will fail because of Primary Key constraints on the history table being violated.

CajunCenturion brings up a good point with related tables. Since I have the entire row in history related tables can be joined but it can be a bit interesting in figuring what the state of the child table was at the time a parent was modified. Basically you have to UNION the History and Original child table together weeding out any orginal table entries that where modified after the parent record and showing the children records that where modified just before the parent record.

This sounds difficult but with a good n-tier design its not bad if you tackle this problem from the start.

 

[alfredjp]

thank you very much for all your posts...
ill try to digest them (being a novice, it would definitely take some time), and try to see if i could somehow come up with a better design...
again, i appreciate it very much.