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

On Dec 9, 12:46 pm, "Mark S. (UK)" <ma..._at_someaccount.net> wrote:
> Even without readings, I have to be able to show that "the sensor is
> installed at this location".

How about:

LocationNames (Location PK, Name UNIQUE)
SensorLocations (Sensor PK, Location FK)
SensorLimits (Sensor PK FK, LowerLimit, UpperLimit)

> 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,

So this is a temporal interval query on SensorReadings, but I don't know how that should be solved - I know temporal databases are considered a problem, but haven't educated myself on the topic yet.

> 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 -

I think this should be a query that finds the latest alert from the union of all different kinds of alerts - some of which are views derived from SensorReadings, others may be base relvars (e.g. SensorFailed (Sensor PK) ).

> however a user must eventually
> acknowledge the alert *and* apply a "corrective action" to it before it
> can be considered resolved.

Ideally, would a relvar with a tuple-valued primary key be suitable here? However, if we obtain the alerts via derivation while the acknowledgement is a base relvar, it might be a problem if the conditions of the alert persist beyond the acknowledgement. Mark, what do you have in mind when the exceptional condition continues after being confirmed?

> 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).

Wrong modeling paradigm. In here, an entity is but a unique value in a domain, everything that can be said of it are propositions in relations.

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

That's just silly, the value will be digitally represented, I doubt you have a real analog computer there. Why not call it temperature, that's what you're measuring, right? In general, I consider suffixes such as _id or _value redundant. Received on Thu Dec 09 2010 - 17:51:55 CET