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

On 07/12/2010 18:42, -CELKO- wrote:
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Please learn to follow ISO-11179 data element naming
> conventions and formatting rules. The singular table names (they are
> sets of more than one element, aren't they?) so use a collective or
> plural name and the use of universal "_id" on every table were pretty
> bad.

Okay, point taken.

> \Sample data is also a good idea, along with clear specifications. It
> is very hard to debug code when you do not let us see it. If you want
> to learn how to ask a question on a Newsgroup, look at:
> http://www.catb.org/~esr/faqs/smart-questions.html

Okay will take a look.

> 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) : -
>
> CREATE TABLE Locations
> (location_id CHAR(16) NOT NULL PRIMARY KEY); --htm number?
>
> Now you start making mistakes. The location of a sensor is NOT an
> attribute; it is a relationship between the sensor, the location and
> readings with a temporal element. Would you have done that with
> people?

When a system is installed, a sensor is always fixed into place - it always has to exist in a location and the location does not change. It is literally wired in (for instance, into a freezer unit).

Even without readings, I have to be able to show that "the sensor is installed at this location".

It is not a mobile sensor; it is static.

Another way of looking at it:

In the system, if we want to monitor the air temperature of a freezer for instance, we'd add a location, set it's name attribute to be "freezer", and place a sensor inside it. This is perminant wired in live monitoring; the sensor does not move around. There can be any number of these sensors to monitor all sorts of things; humidity, air temp, water temp, etc.

I often wonder if "Sensor" is the wrong name for the table - it is basically an element of the location that we are measuring, i.e. "air".   The upper and lower limit is the limits to which the measured element must stay between - and if it goes outside of these limits (for a configured length of time) an alert is generated.

For instance, if the air temperature goes above its set limit for too long, an "air too warm" alert will be generated. That same alert stays active until the air temperature drops below the upper limit, or until another alert takes it's place. For instance, if the sensor fails it would become an "air sensor failure" alert. The "air too warm" alert then enters the "inactive" state - however a user must eventually acknowledge the alert *and* apply a "corrective action" to it before it can be considered resolved.

That is why I modelled an alert as an entity - it has state (though it might not be a good enough reason to model it as an entity).

>
> CREATE TABLE Sensors
> (sensor_nbr CHAR(15) NOT NULL PRIMARY KEY, --gtin
> upper_reading_value INTEGER NOT NULL, -- hardware imposed limits?
> lower_reading_value INTEGER NOT NULL,
> CHECK (upper_reading_value>= lower_reading_value))
>
> Why dd you think you need a "sensor_reading_id " at all? How did you
> have a reading without a sensor in the relationship? Don't use vague
> names like "value" and reserved words like TIMESTAMP for data
> elements.

Point taken - the sensor_reading_id is not needed.

I used value because it can represent any analog reading (temperature, humidity, etc.) - so maybe I should of called it analog_value.

> Try this. The reading events of a particular sensor are held in time
> and space, so model it. These co-ordinates locate a reading and a
> status in those dimensions.
>
> CREATE TABLE Sensor_Readings
> (sensor_nbr CHAR(15) NOT NULL
> REFERENCES Sensors (sensor_nbr ),
> location_id CHAR(16) NOT NULL
> REFERENCES Locations(location_id),
> PRIMARY KEY (sensor_nbr, location_id, reading_timestamp),
> reading_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
> reading_value INTEGER NOT NULL,
> sensor_status CHAR(6) DEFAULT 'normal' NOT NULL
> CHECK(sensor_status IN ('alert', 'normal'))
> );
>
>>> The last table [SensorAlertReading in your original] is associating readings with the alert, because it is the reading that dictate that the sensor is in alert or not.<<
>
> Read what you wrote; this is a computed value; it is not an entity or
> a relationship, so it should not be in a separate table. You might
> also use a computed column or a VIEW rather than a simple column. If
> the rule is simple, use the GENERATED; if not, go to the VIEW so you
> can use other tables.
>
> sensor_status CHAR(6) NOT NULL
> GENERATED ALWAYS AS
> (CASE WHEN reading_value> 42 --whatever the rule is
> THEN 'alert' ELSE 'normal' END)

Again point taken, but how do I cope with the alert's state? The database must store that a user "acknowledged" the alert, and that a user applied an "action" (an action is an entity defined in another table "actions").

Would it be something like this:

[AlertAcknowledgement]
sensor_nbr [FK]
user_id [FK]
acknowledged_at TIMESTAMP

[AlertAction]
sensor_nbr
user_id [FK]
action_id [FK]
actioned_at TIMESTAMP

At this point I don't have much integrity - the alert I acknowledged and actioned is not an entity, so it is computed from readings. Rememeber an alert spans many readings..

Mark. Received on Thu Dec 09 2010 - 11:46:10 CET