Looking for elegant solution to sensor / reading / alert database design.

From: Mark S. (UK) <"Mark>
Date: Thu, 02 Dec 2010 13:24:26 +0000
Message-ID: <t9adnbSl-4JhAGrRnZ2dnUVZ8jKdnZ2d_at_bt.com>



Hi all,
I'm designing a database that consists of the following: There can be many locations.
Each locations can have 0 or many sensors. Each sensor can have many readings.
A sensor is either in "alert" state or "normal" state depending on the last reading.

An alert is an entity that must be acknowledged, acted upon, etc.

Initially I related readings to alerts like this (very cut down) : -

[Location]

LocationId

[Sensor]

SensorId
LocationId
UpperLimitValue
LowerLimitValue

[SensorReading]

SensorReadingId
Value
Timestamp

[SensorAlert]

SensorAlertId

[SensorAlertReading]

SensorAlertId
SensorReadingId

The last table is associating readings with the alert, because it is the reading that dictate that the sensor is in alert or not.

The problem with this design is that it allows readings from many sensors to be associated with a single alert - whereas each alert is for a single sensor only and should only have readings for that sensor associated with it (should I be bothered that the DB allows this though?).

I thought to simplify things, why even bother with the SensorAlertReading table? Instead I could do this:

[Location]

LocationId

[Sensor]

SensorId
LocationId

[SensorReading]

SensorReadingId
SensorId
Value
Timestamp

[SensorAlert]

SensorAlertId
SensorId
Timestamp

[SensorAlertEnd]

SensorAlertId
Timestamp

Basically I'm not associating readings with the alert now - instead I just know that an alert was active between a start and end time for a particular sensor, and if I want to look up the readings for that alert I can do.

Obviously the downside is I no longer have any constraint stopping me deleting readings that occurred during the alert, but I'm not sure that the constraint is neccessary.

Now looking in from the outside as a developer / DBA, would that make you want to be sick or does it seem reasonable?

Is there perhaps another way of doing this that I may be missing?

Here's another idea - it works in a different way. It stores each sensor state change, going from normal to alert in a table, and then readings are simply associated with a particular state. This seems to solve all the problems - what d'ya think? (the only thing I'm not sure about is calling the table "SensorState", I can't help think there's a better name (maybe SensorReadingGroup?) : -

[Location]

LocationId

[Sensor]

SensorId
LocationId

[SensorState]

SensorStateId
SensorId
Timestamp
IsInAlert

[SensorReading]

SensorReadingId
SensorStateId
Value
Timestamp

There must be an elegant solution to this!

Cheers,
Mark. Received on Thu Dec 02 2010 - 14:24:26 CET

Original text of this message