Collecting audit and statistical data

My problem is that I have a lot of events happening in a large web application and now and then I want to see what happened (for auditing purposes) or I want to aggregate the data for statistical reporting.

One solution would be to create a table in the DB for each type of event and log it there. e.g. a password is changed, log the date, user, ip etc. This will provide me with the audit information I need and also the ability to run reports against the table to see how often this functionality is used. The downside is that I would need to create a new table for each type of event that I want to capture.

My ideal solution would be to have a single table with a more flexible structure, perhaps an XML field, but I'm not crazy about the xml field being in the table.

So my question: Is there a well used (pop开发者_如何学Goular) pattern that addresses my problem?

How large is your large web application?

Logging events as XML blobs should work, and some databases (e.g. SQL Server) let you query that XML directly. However, the performance of these queries is terrible.

Before you do event logging in the database, you should figure out how many records per second you're going to create. If the number is large it is going to put serious load on your database and could affect your overall application performance. Also, once you accumulate a large number of records, querying the data would take forever (and kill db performance in the process). Aggregating the data is even worse - relational databases aren't very efficient in doing aggregations.

Chris' suggestion above would work well for small databases, but won't scale since your queries will have to use joins. It may be better to de-normalize your data.

Even if your application isn't getting enough traffic for you to worry about this right now, keep in mind that event logging to the DB won't scale well for the reasons explained above.

Concreate suggestions:

If you don't have that much traffic and decide to log to the DB, do this to a separate schema, so that it'll be easier for you to move it to a separate db server in order to offload it from your production database.

If you decide to log the event as an xml, consider whether there a point in using a relational database for the purpose - if you can't query that efficiently, then simple log files would be much simpler. You'd have to figure out how to process that log data later on of course, but for infrequent / simple queries, writing some scripts using grep, awk, etc. would take you a surprisingly long way.

The method commonly used nowadays by (very) large scale applications is logging to files, then running your analysis (aggregation) using map-reduce, e.g. on hadoop.

An intermediate way between one table per event and one table is (assuming that the difference between events is the parameters/data carried with the event):

Event Type
  Event Type Id (PK)
  Name
  Number of parameters (useful - not essential)

Event
  Event Id (PK)
  Event Type Id (FK)
  Timestamp

Event Attribute
  Event Attribute Id (PK)
  Event Id (FK)
  Name 
  Value (as string in all cases)
  Sequence Number (within Event. this may well not be needed, but can be a convenience)

I don't think this is a named pattern, but it is a pattern that comes up repeatedly in database design.

I think this gives you all the information you need, without the need to store XML.