I have a very busy website. Users can create many types of content, which can then be voted up/down, reported as offensive, enabled/disabled by admins, etc, etc. I want to enable auditing of all actions that are performed on the site.
The database I'm using is SQL Server 2005. I have two options in my head. The first is a single, large table which contains most of the details required for the audit screens, i.e. snippets of content are duplicated in this table to remove the need to join to other tables. The second solution is to have a separate audit table for each type of content. This makes it more complex to retrieve an audit log for a user, for example, because they may be involved with many different types of content and therefore I'll need to retrieve data from many different tables.
So it seems to be a trade-off between table size and query simplicity. Are there any rules of thumb here – or perhaps it's blindingly obvious which way is better?
Best Answer
This doesn't exactly answer your question, but the document is so darned helpful that you'll really benefit from reading it. Microsoft put together an auditing & compliance guide for SQL Server 2008 (I know, not 2005) and it contains a ton of great info about the subject:
http://www.microsoft.com/downloads/details.aspx?FamilyId=6E1021DD-65B9-41C2-8385-438028F5ACC2&displaylang=en
The situation you're describing is a great candidate for SQL Server 2008's Change Data Capture, which automatically captures the data you want to manage. Unfortunately, though, it's Enterprise Edition only, which means it's not cheap. If you want to avoid reinventing the wheel, it's the way to go.
When you say "very busy web site", it sets off red flags though. None of these techniques, even rolling your own, are going to be low-overhead.