We have a system that does some calculations for a network consisting of nodes, edges and endpoints. This data is stored in a Postgresql DB. Each of these tables have entries for different attributes (5-10) measured in 15min intervals and all of them have a column with a timestamp (ideally they should all should have entries for the same time at a specified timestep).
Would you consider it a good design decision to create an extra table with all timesteps in 15min intervals from e.g. 1970 00:00:00 ... 2100 23:45:00 and reference the indexes of this table instead of saving the timestep as timestamp in each of the tables? Or are there other, better alternatives?
Just for illustration we'll use a traffic simulation. We have a tables for topology; nodes (crossroads and bends) connected by edges (streets) and garages with a few base attributes like location, capacity, max speed,.... Then we have time series data for each of these nodes and edges, so for each of these edges and nodes we have measured data at 15 min intervals (# of cars, width of cars, etc..). That means around 35.000 entries per node, per edge, per endpoint etc... This means that there will be a LOT of data pretty quickly (depending on the network size of course) also if we also have multiple networks in the same DB/Schema this multiplies quickly.
We then use this data to extrapolate additional data or fill in the gaps (when we know the amount of cars and capacity we only need a few node/endpoint measurements to simulate the whole traffic flow). So we have at least the duplicate amount of tables (streets measured and streets simulated).
First of all a surrogate key series data would mean all entries (for a specific time step) would be exactly the same (sometimes measurements differ in the range of minutes) Second, even saving two bytes per entry would amount to quite some saving if we have enough nodes and edges.
Third, I guess the basic design has to be revised in the future anyway but for now I'm stuck with this design so I need to improve what I currently can (insertion for ex. is also quite a pain).
E.g. one year, network with 100 nodes, 100 edges, 50 endpoints, each 5 attributes in double precision:
35000*250*15*8 ~ 1GB
double that for the simulated data and we already have 2GB of data for a relatively small network and one year of data without the timestamps.
Database redesign opportunity: What table design to use for this sensor data collection?
Design for scientific data. Data table with hundreds of columns or data table with a generic value column and hundreds of rows (EAV)?