Yes! I’d absolutely recommend using a relational table that has the metadata, so something like:
CREATE TABLE sensors(
type integer, --might have a types table?
And then a hypertable that has the sensor_id in it:
CREATE TABLE sensor_readings (
sensor_id bigint, -- could make a foreign key
SELECT create_hypertable('sensor_readings', 'ts');
Then you can use a join to get the metadata from the
sensors relational table.
You might want to make that id a foreign key, but there are performance tradeoffs there, so it depends on your application. If peak performance isn’t what you’re going for, then making it a foreign key is probably a good idea. You can always enforce in the application essentially, but of course I’d prefer to enforce in the database.
This is sort of the typical more “normalized” relational model. There may be times when you want to “denormalize” or, essentially put some of those thirty columns back into the hypertable. I’d say that there are a few cases when you’d want to do that:
- if you update the column on the relational table but you want the old value associated with the reading, so if it’s like a setting you might wnat to include it in the reading instead of in the relational table - for instance if the sensor moves, you might want the coordinates associated with the reading rather than with where the sensor is now. (You could also model as a slowly changing values table, but that’s a different story for a different day. Another way would be if there are other values that are infrequently updated but you want associated with settings, you might need to have a proxy sensor id and generate a new one when those values are updated, but again, more complex consideration for later most likely).
- If you often query along a dimension it might make sense to denormalize it to improve performance. So, if you often want to, say, group by owner and do some aggregations across sensors (which might or might not make sense), then you might want to put owner in the hypertable so you don’t need to do the join to to the group by and you can instead join after the group by (when you’ll have far fewer rows).
Anyway, I hope that was helpful! Let me know if that was the question you were asking or if I missed what you were asking completely!