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:
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.
A reference table of
event_subtypes, and a generic, single
logstable 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.