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?