I'm designing a timeseries oriented database and I'm torn a bit on how to manage the dimension table for the history table. The history table stores a configuration_id, timestamp, and value, and there's a separate configuration table that captures "how" that value was recorded. Because of how amorphous/disparate the configuration info is, I chose to use a JSONB column to store it (this table won't be queried by the json properties, so it should be fine). The configuration can change at some point, so it's timestamped and old configurations are kept for historical context. It is basically another history table. For values that are continuous, this makes sense, since they can come from very different sources with separate sets of variables.

However, there is also data intended to be stored in the history table that is discrete, and the value is just an integer index in a dictionary. This dictionary might change over time, so it makes sense to treat it as configuration blob. However, I could also create a dedicated dictionary table of (measure_id, key, value, timestamp), and it would support all dictionary-based measures while storing a history of changes. It can also support a FK, but I don't think FK's can be conditional on time ranges. Still, this seems better from a relational-db perspective, but I now have two data models to deal with instead of one.

So my question is, which is better - to stick to the the relational model where possible, or to be consistent? Or does not having FK support defeat the point of the relational model entirely?

  • Have you considered using a dedicated timeseries solution like TimescaleDB or Citusdata?
    – Vérace
    Mar 29 at 22:12


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.