Hi, I need to store data about sensor readings. There is a known (but configurable) number of sensors which can send update data at any time. The "current" state needs to be kept but also all historical records. I'm trying to decide between these two designs:
1) create a table for "current" data, one record for each sensor, update this table when a sensor reading arrives, create a trigger that would transfer old record data to a history table (of basically the same structure) 2) write only to the history table, use relatively complex queries or outside-the-database magic to determine what the "current" values of the sensors are. The volume of sensor data is potentially huge, on the order of 500,000 updates per hour. Sensor data is few numeric(15,5) numbers. I think the second design would be easiest on the database but as the current sensor state can potentially be queried often, it might be too slow to read. Any recommendations?
signature.asc
Description: OpenPGP digital signature
