Database Architecture for logging

Question

This is something that has bothered me for a long time and i still have been unable to find an answer.

I have a huge system with alot of different features. What is common for this system is of course that my users can

create, update, read & delete

different parts of my system.

For simple reasons lets say i have an application that has the following features:

Document administration
Video administration
User administration
Salery administration

(Please do note i took these at random just to prove a point that all of these would have their own separate tables and does not necessarily be connected).

Now i wish to create some sort of logging system. So that when ever someone either create,update or delete an entity it will be recorded.

Now as far as i can see i can do this two ways.

1.

Create a logging table for each of the 4 features that is in my system. However with this method i am required to create a logging table for each new feature i add to the system. i would also have to combine data from X number of tables if i wish to create a log which potentially could be a huge task!

2.

i could create something like the following:

However once again i would have to add a col for each new feature i will add.

So my question is what is the best way for creating logging database architecture

Or is there an easier way?

You can just have an `actionTaken` and `dateOfAction` field and insert a specific action everytime a user add, alters or delete anything from the database. — Cyval, Jan 05 '16 at 10:47

Phiter · Accepted Answer · 2016-01-05T11:04:23.043

1

Instead of one target_xx for each feature, you could do it this way:

target_id | target_type
1           video
4           document
5           user
2           user

or even better. A table with target types and insert only the respective id's on target_type

Something like this:

edited Jan 05 '16 at 11:04

answered Jan 05 '16 at 10:51

Phiter

14,570
14
50
84

score 1 · Answer 2 · answered Jan 05 '16 at 10:55

if you want to capture for each table creation and update date, i would just use the default and the update event from mysql. You can define the fields like this for a table:

ALTER TABLE table 
ADD COLUMN CreateDate Datetime DEFAULT CURRENT_TIMESTAMP, 
ADD COLUMN LastModifiedDate Datetime ON UPDATE CURRENT_TIMESTAMP;

You can add these 2 fields in all tables. If you want to use one central table for logging (which might be more difficult to manage, because you always need to create joins, maybe also worse performance), then I would work with triggers.

Database Architecture for logging

2 Answers2