I've been confronted by this problem before and am being faced with it again while working on a new big project (YUMPS
The question is how to best structure a logging database table.
It seems like it should be a trivial issue. You want to keep a table of events that an administrator can browse to help identify problems. So you obviously need fields for date/time stamp, and some text about the event, maybe the userid# responsible.
Now if you're only going to log unusual rare events, and you're never going to need to search it, then there's nothing to worry about.
But if you would like to log a lot of events to help you keep records and track what users are doing, and want to be able to search it easily, then it starts to get a little tricky because this log table might start getting huge, hundreds of thousands or even millions
of entries.. So do you really want to be storing large text blocks? Some of these events you might like to preserve indefinitely, while others are just going to be taking up huge amounts of wasted space..
And do you need to reserve some fields in the table corresponding to object ids relevant to the event. For example if there is an event like "User X did operation Y on Blog post Z" and you ever want to be able to search for all operations on blog post Z, then you need an integer field for Z so you can search and filter for all events on that column=Z. But its not going to be a specific column for BlogPostId because blog posts are only 1 of the kinds of objects you work with. So it gets messy.
An alternative solution would be to use multiple log tables, one for each kind of event.. I guess this has some pros and cons.
Anyone know about any good discussion of this kind of thing? Or any particularly elegant solutions? It's obviously something that the big guys (facebook, etc.) need to deal with well, but i've not seen any good discussions of the issue.