I have two applications communicating over a LAN via events contained in network messages. Each message (i.e. an UDP packet) contains only one event. Messages are heterogeneous, meaning that the first "field" (always an integer) is the message type, the second is a timestamp (always an integer). The other fields differ depending on the message type.
I want to log those messages into a database. Messages are totally unrelated and there is no relationship between them (since they are related to different events). My logger application receives packets and assign them an unique incremental integer.
Basically I'd like to know if there is a standard way to store those packets/events in a database. The most useful SQL query I'll need is to get all events in the same order I stored them.
So far I come across two possible implementations.
For each event type I can create a different table, plus a "main table":
Table Main: Counter (INTEGER Primary Key), Timestamp (INTEGER), MessageType(INTEGER) // MessageType is optional but useful Table Msg1: Counter (INTEGER FOREIGN KEY), Name(STRING), Surname(STRING) ... Table Msg2: Counter (INTEGER FOREIGN KEY), FieldFoo(INTEGER), FieldBar(INTEGER)... ...
If I need to get all the events I have to make a JOIN using the tables. Giving the fact I might have 15-20 events I could have a very huge JOIN.
Given that 90% of fields are INTEGER, I could create a maxitable:
Table Huge: Counter(INTEGER Primary Key), Timestamp(INTEGER), MessageType(INTEGER), Field1(INTEGER) ... Field5(INTEGER), Field6(STRING), Field7(STRING)...
Basically I'm not giving a role to fields here, and their meaning depends on the field MessageType. Not a very elegant solution but prevents me to make a JOIN using 15-20 tables. I can still filter events of the same type thanks to the MessageType field.
Efficiency is not a problem in the inserting phase here because event packets are quite rare (and I'm aware of the fact that solution #1 requires two inserts for each event).
From the point of view of my application there isn't a very big difference: both solutions are just fine for my needs since I can get the very same information from both solutions. Solution 1 seems more elegant (at least it's "normalized") but the idea of making a JOIN using 15-20 tables makes sound a ringbell in my head. I use SQLite by the way, does it have a limit on how many tables I can join on the same SELECT query?
Is there a standard way to approach this kind of problem (i.e. logging heterogeneous events)? My guess is that this is a common problem and thus there is a standard solution.