CODEWITHSUNDEEP
postgresqldatabase-designrelational-database
guettli
guettli

Reputation: 27969

Structured Logging in a relational database

How to design the relational database layout to capture structured logging?

Usecase 1

The output of sensors should be logged. Data: temperature and sensor-id.

Usecase 2

The duration of web requests should be logged. One entry for every request. Data: URL and duration

Common data

The two usecases are just examples. There could be much more. Each log entry should have a timestamp and a source-host column

Relational

Please don't tell my to use noSQL. This particular question is about a relational database layout. :-)

Our preferred database is PostgreSQL, but this should not matter here.

Upvotes: 0

Views: 251

Answers (1)

user_0
user_0

Reputation: 3363

In similar case I used, and suggest, separate tables.

If you can use postgresql, you can take advantage from inheritance.

In your case you can use a master_table for common_data and inherit the others table.
You wrote you prefer postgresql, so I assume you know, but, just in case: http://www.postgresql.org/docs/9.4/static/tutorial-inheritance.html

Using this you will take advantage of specific indexes in your data.

Another way is to use a single table with common data and other data in a json or hstore data, but this is close to have a nosql db. And no real advantages in this case, except faster coding.

Upvotes: 1

Related Questions