2

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.

Implementation 1:

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.

Implementation 2:

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.

4 Answers 4

3

It seems that the very same question has been asked (and answered) on stackoveflow:

https://stackoverflow.com/questions/2797592/best-practice-logging-events-general-and-changes-database

1

I think the standard way is to use syslog on Unix systems, Event log on Windows systems.

0

This reminds me of a technique I use, but rather than logging directly to the DB, bulk loading flat-file logs for analysis using SQL. I have no problems at all loading logs from many systems into a single table. So your option 2 I would recommend.

0

I'm pretty sure syslog-ng supports *SQL. A google search revealed this.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.