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

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:

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:

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

CodePudding user response：

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;