I previously wrote a little about a generic schema: https://wiki.scel-hawaii.org/doku.php?id=user:kluong:thinking_about_a_generic_schema

I didn't quite get to finish my thoughts, however. Thinking about it a little more, I think the structure the database should be something closer to this:

CREATE TABLE IF NOT EXISTS weather_metrics (
   time TIMESTAMP WITH TIME ZONE NOT NULL,
   node_id int NOT NULL,
   temp_c double PRECISION NULL,
   pressure_hpa double PRECISION NULL,
   humidity_percent double PRECISION NULL,
   ... # fill in the other fields here
);

Each type of data we're collecting should be present as a field. Just like in my previous post, if a field is unimplemented, it doesn't waste too much space if it's empty.

Building it this way has the benefit of making the database pretty readable - it's easy to understand what data we're storing by just looking at the column names.

Contributing authors:

Created by kluong on 2022/02/16 00:04.