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?