Audit Trail : Single table for all users or multiple table per user
-
Hi, I'm designing an audit trail database to log user's activities. Should I create a single table to log all user activities e.g:
Id(PK) | UserId(FK) | ActionTime | ActionTaken
or, multiple tables; each per user e.g:
Table Name : AuditTrail_UserId
Id(PK) | ActionTime | ActionTaken
I'd like to be able to do 2 kind of text search though, 1. Search single user activities 2. Search all user activities
-
Hi, I'm designing an audit trail database to log user's activities. Should I create a single table to log all user activities e.g:
Id(PK) | UserId(FK) | ActionTime | ActionTaken
or, multiple tables; each per user e.g:
Table Name : AuditTrail_UserId
Id(PK) | ActionTime | ActionTaken
I'd like to be able to do 2 kind of text search though, 1. Search single user activities 2. Search all user activities
-
Hi, I'm designing an audit trail database to log user's activities. Should I create a single table to log all user activities e.g:
Id(PK) | UserId(FK) | ActionTime | ActionTaken
or, multiple tables; each per user e.g:
Table Name : AuditTrail_UserId
Id(PK) | ActionTime | ActionTaken
I'd like to be able to do 2 kind of text search though, 1. Search single user activities 2. Search all user activities
-
Separating the tables per-user is not necessary. Make sure that you specify that the user ID is an index and the RDBMS will do all of the heavy work for you - making sure that internally the table is structures in such a way that queries such as: select * from audit where UserID = 'someuserid' are very rapid. It would be well worth examining the documentation on CREATE INDEX: http://msdn.microsoft.com/en-us/library/ms188783.aspx[^] Careful usage of features such as these will give a much more performant solution that trying to roll your own equivalent (by separating into tables per user).
-
Separating the tables per-user is not necessary. Make sure that you specify that the user ID is an index and the RDBMS will do all of the heavy work for you - making sure that internally the table is structures in such a way that queries such as: select * from audit where UserID = 'someuserid' are very rapid. It would be well worth examining the documentation on CREATE INDEX: http://msdn.microsoft.com/en-us/library/ms188783.aspx[^] Careful usage of features such as these will give a much more performant solution that trying to roll your own equivalent (by separating into tables per user).
-
Hi, I'm designing an audit trail database to log user's activities. Should I create a single table to log all user activities e.g:
Id(PK) | UserId(FK) | ActionTime | ActionTaken
or, multiple tables; each per user e.g:
Table Name : AuditTrail_UserId
Id(PK) | ActionTime | ActionTaken
I'd like to be able to do 2 kind of text search though, 1. Search single user activities 2. Search all user activities
eight wrote:
1. Search single user activities
2. Search all user activitiesI don't see any reason to put them in separate tables. If it's the same data, with only a single different property, then it should go in the same table and the extra property becomes part of the key.
I are Troll :suss:
-
Hi, I'm designing an audit trail database to log user's activities. Should I create a single table to log all user activities e.g:
Id(PK) | UserId(FK) | ActionTime | ActionTaken
or, multiple tables; each per user e.g:
Table Name : AuditTrail_UserId
Id(PK) | ActionTime | ActionTaken
I'd like to be able to do 2 kind of text search though, 1. Search single user activities 2. Search all user activities
Hi, Just throwing few built-in mechanisms which could be usable (without knowing the backgrounds for your audit needs). Have you had a look at Change Tracking and Change Data Capture: http://msdn.microsoft.com/en-us/library/cc280519.aspx[^]. Then SQL Server also has several different mechanisms for pure auditing: http://msdn.microsoft.com/en-us/library/cc280526.aspx[^] but some of those are edition specific.
The need to optimize rises from a bad design.My articles[^]