Database design for logging varied types of data 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_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.
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.