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.

Mark S. (UK) wrote:

[snip]

First off, if you're lucky most of the participants in this group will
ignore your request. If you're not then one of them may take the
opportunity to snarl at you, possibly accusing you of asking for a
homework solution or some other plausible crime.

I'm more soft-hearted so I won't do that, but I also won't prescribe a
solution. There are various reasons, not least being that I know
nothing about your business problem.

I do want to address your desire for an "elegant" solution though.

I certainly accept that there are database designs that are grotesque or
tortured or poorly matched to reality, but there really isn't any way to
judge the elegance of a database design. Furthermore, database
designers typically have a misplaced eagerness to "simplify" their
designs, which usually means minimizing the number of tables. The
result of that simplification is that real-world complexity is removed
or concealed instead of being highlighted and supported.

The complexity doesn't go away because the designer has abstracted or
concealed the details. All the database designer will have done is
shift the burden of understanding and managing the detail onto the
application software (and the application programmer).

Your test should be two-fold: does my database design allow me to assert
all the facts I need, and does my database design make it difficult to
accidentally formulate incorrect queries to answer reasonable
questions? If the design passes those tests yet it has a
necessary proliferation of tables and foreign keys and constraints, it's
a good design. (Furthermore, there is no incontestibly correct
design--it will be one of many possible good designs).

HTH

--
Roy

Thanks Roy.
And appologies for posting in this forum if it was inappropriate. I'd
love to be young enough again for this to be a homework question! The
main reason I posted here was to get responses from people who are into
how databases should be, not just good enough for it to work. Most of
the time when I ask questions elsewhere, I get people suggesting I
should use NULL FKs and the like, which does not sit right with me
(rightly or wrongly).

Sometimes someone just pointing out ways to solve something in another
way can really open doors in your mind and make things suddenly fall
into place.

Anyway, I appreciate you're response; the latter part about testing the
database was especially helpful.

Thanks,
Mark.


On 03/12/2010 13:12, Roy Hann wrote:

On Dec 6, 1:18*pm, "Mark S. (UK)" <ma... (AT) someaccount (DOT) net> wrote:
The group is mostly focused on theory - the relational model of
databases, as well as related fields such as set theory and first-
order logic. Specific designs are difficult to discuss here, since
each vendor's idiosyncracies contradict the requirements and
recommendations of the model. You could try vendor-specific forums,
but as you suspect, the recommendations there are often based on
convention, heuristics or product features, rather than on logic.

I would like to suggest that your design is contaminated by naive and
mereological realism. A database should not represent objects, it
should represent facts about objects. Take, for example, your table:

Based on my own assumptions to fill in the information you didn't
provide (or which I may have missed), I would say this table describes
at least three different facts or relations:

Sensor <SensorId> is located at <LocationId>
Sensor <SensorId> has upper limit <UpperLimitValue>
Sensor <SensorId> has lower limit <LowerLimitValue>

Do you intend a 1-to-1 correspondence between the three facts? Are
you only modeling installed sensors with both lower and upper limits?
This may be a join dependency, and join dependencies, like all
dependencies, are significant in a database. Study the relational
normal forms and apply it to your design.

Nilone

On 06/12/2010 13:03, Nilone wrote:
Yep in this case I did intend a 1-to-1 correspondence. A sensor always
represents installed sensors at a particular location, and always has an
upper and lower limit.

One of the reasons I posted here is that it seems the requirements I
have just cannot yield a totally clean schema, and neither can others I
have asked (mostly on StackOverflow). The closest seems to be this:

http://www.thejunkroom.co.uk/~marks/MarkDM.pdf

Derived from the discussion here:

http://stackoverflow.com/questions/4...atabase-design

I don't think it's possible to get a totally clean solution using the
relational model *without* creating a LoggerReadings table and repeating
all of the Alert tables especially for the LoggerReadings. I thought
that might be an interesting challenge for the theorists.

In the schema above there's a special case for "Sensor" where the field
"SensorNo" equalling ZERO means it represents a Logger, therefore
allowing the Readings table and associated Alerts table to be used.

If anyone wants to take a look and can think of any other ideas then
please feel free.

If not, I may well just go with a variant of this schema as it seems the
cleanest idea so far.

Thanks,
Mark.

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.

\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

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?

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.

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'))
);

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)

On 07/12/2010 18:42, -CELKO- wrote:
Okay, point taken.

Okay will take a look.

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

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.

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.

Mark S. (UK) wrote:

Ignore Joe. This isn't some product-specific or even language-specific
technical support group. It's the fucking theory group. Joe is an idiot.

On Dec 9, 12:46*pm, "Mark S. (UK)" <ma... (AT) someaccount (DOT) net> wrote:
How about:

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

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.

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

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?

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.

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.

On Dec 9, 12:46 pm, "Mark S. (UK)" <ma... (AT) someaccount (DOT) net> wrote:
Based on the suggestions in my previous post, you could use a view
like the following to find the length of time sensors are out of
bounds.

CREATE VIEW SensorLimitsExceeded AS
SELECT a.Sensor, a.Time, a.Reading, a.Time - b.Time AS AlertDuration
FROM SensorReadings cur, SensorReadings prv, SensorLimits lim
WHERE cur.Sensor = prv.Sensor
AND cur.Reading NOT BETWEEN lim.LowerLimit AND lim.UpperLimit
AND prv.Time = (SELECT Max(hst.Time) FROM SensorReadings hst,
SensorLimits lmh
WHERE hst.Sensor = cur.Sensor AND hst.Sensor = lmh.Sensor
AND hst.Time < cur.Time
AND hst.Reading BETWEEN lmh.LowerLimit AND lmh.UpperLimit)