Search code examples
databasepostgresqlloggingtracking

Database Design - How to store and use flexible custom user events?

I am trying to wrap my head around how to design a database (in my case postgresql) to store flexible and custom events. The idea is not to have to create different rows for each new event I want to track.

Here is a first idea of how it could be:

enter image description here

The problem here is that it then becomes quite hard to analyse with other tools (e.g. excel or Tableau) as now the traditional name of the row is contained as a value.

Given that data before, here is a structure that could be better for analysis as the row names are the specific events:

enter image description here

To summarise my question: how can I go about storing events with variable key and different types of value properly?

Solution

  • Go with the original key/value design.

    The structure of your table has no influence on the presentation of data, for example if you export them for use with a spreadsheet. When exporting data, you can always use a query like

    SELECT timestamp, user_id,
       jsonb_object_agg(key, values)
FROM (SELECT timestamp, user_id, key
             jsonb_agg(value) AS values
      GROUP BY timestamp, user_id, key) AS q
GROUP BY timestamp, user_id;