Database design for logging varied types of data

Question

I am currently in the process of moving our logging system from file-based to database-based, and I'm trying to come up with a suitable schema to fit. I'd like my application to be logged extensively, with dozens of possible event types (and some with sub-types) that I'd like logged. Each event I log will have different data logged with it, depending on the event type.

My end goal here is to be able to present the data on a website, and perhaps run some statistics on it later easier than traditional log files would allow. For context, my application is a gameserver and mostly involve logging ingame player actions.

So far, I've thought of a couple of approaches to doing this:

1. I could create tables for each event I want to log. This would be fine if I were only logging a few events, but I would rather not have dozens of tables - one for logging each specific thing. The advantage of this is that I don't have to worry about storing the data associated with each event - each table would have specific fields for the data I'm storing. I also imagine presenting this data would be a challenge - probably involving JOINs across many tables.

2. A reference table of event_types and event_subtypes, and a generic, single logs table for storing the actual log data. My concern here is: how do I store the data associated with each event? This data is different depending on what event is being logged. I thought of storing this data as JSON inside a generic 'data' field, but that would severely limit my ability to query the data later, so I'm out of ideas. Another option is the EAV model which is new to me, but I hear a lot of criticism of it so I'm wary.

A couple of examples of events I'll be logging, to further illustrate the context here:

• A player sends a chat message. I'd like to store the player's account ID and the message text.
• An admin uses an admin action on the player. I'd like to store the admin action type, the player's account ID, the admin's account ID, and the reason.

My question is: what would be the best approach here given my requirements? I'm leaning towards the second option, but would value any input regarding alternative approaches or something I haven't considered.

Answer 1

I've thought of a way to do it which isn't ideal but it should work.

Rather than naming the fields in relation to what they store (in terms of the log data) you could simply name them LOG_ARGUEMENT_1, LOG_ARGUEMENT_2, LOG_ARGUEMENT_N ... And have a permanent field for ACCOUNT_ID which is a foreign key to an ACCOUNT table containing information about each user (such as if their an admin or not and when they last completed and action) Also have an EVENT / EVENT_SUB_TYPE table as you suggested linked to the main LOG table. Finally you could also have a field with the full log on a separate table linked to the log table for error checking.

Answer 2

I would recommend one of two approaches. I agree the "have a table for every event type" might get annoying over time.

1. Come up with a generic event table where most of your events can have conformed messaging. Keep the idea of event and event types and create a few more generic fields like severity and user_id that are widely relevant. For things that you can't make generic, have a few grab-bag columns you can stuff things into.
2. The JSON idea isn't as bad as it once was as many RDBMSs (e.g. SQL Server, Postgres) have pretty good support for dynamic parsing of JSON. If you record the type of event you're logging with a type ID, you could later run a SELECT query that parses the JSON up into normal columns for all rows matching that type.