sql - Opinions on sensor / reading / alert database design

Question

I've asked a few questions lately regarding database design, probably too many ;-) However I beleive I'm slowly getting to the heart of the matter with my design and am slowly boiling it down. I'm still wrestling with a couple of decisions regarding how "alerts" are stored in the database.

In this system, 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
Status
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
Status
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?

Thanks.

EDIT: 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
Status
IsInAlert

[SensorReading]
SensorReadingId
SensorStateId
Value
Timestamp

There must be an elegant solution to this!

Answer 1

Revised 01 Jan 11 21:50 UTC

Data Model

I think your Data Model should look like this:?Sensor Data Model?. (Page 2 relates to your other question re History).

Readers who are unfamiliar with the Relational Modelling Standard may find ?IDEF1X Notation? useful.

Business (Rules Developed in the Commentary)

I did identify some early business Rules, which are now obsolete, so I have deleted them

These can be "read" in the Relations (read adjacent to the Data Model). The Business Rules and all implied Referential and Data Integrity can be implemented in, and thus guaranteed by, RULES, CHECK Constraints, in any ISO SQL database. This is a demonstration of IDEF1X, in the development of both the Relational keys, and the Entities and Relations. Note the Verb Phrases are more than mere flourish.

Apart from three Reference tables, the only static, Identifying entities are Location, NetworkSlave, and User. Sensor is central to the system, so I ahve given it its own heading.

Location

• A Location contains one-to-many Sensors
• A Location may have one Logger

NetworkSlave

• A NetworkSlave collects Readings for one-to-many NetworkSensors

User

• An User may maintain zero-to-many Locations
• An User may maintain zero-to-many Sensors
• An User may maintain zero-to-many NetworkSlaves
• An User may perform zero-to-many Downloads
• An User may make zero-to-many Acknowledgements, each on one Alert
• An User may take zero-to-many Actions, each of one ActionType

Sensor

• A SensorType is installed as zero-to-many Sensors

• A Logger (houses and) collects Readings for one LoggerSensor

• A Sensor is either one NetworkSensor or one LoggerSensor

  • A NetworkSensor records Readings collected by one NetworkSlave
    .
• A Logger is periodically Downloaded one-to-many times
  • A LoggerSensor records Readings collected by one Logger
    .
• A Reading may be deemed in Alert, of one AlertType
  • An AlertType may happen on zero-to-many Readings
    .
• An Alert may be one Acknowledgement, by one User .
• An Acknowledgement may be closed by one Action, of one ActionType, by one User
  • An ActionType may be taken on zero-to-many Actions

Responses to Comments

1. Sticking Id columns on everything that moves, interferes with the determination of Identifiers, the natural Relational keys that give your database relational "power". They are Surrogate Keys, which means an additional Key and Index, and it hinders that relational power; which results in more joins than otherwise necessary. Therefore I use them only when the Relational key becomes too cumbersome to migrate to the child tables (and accept the imposed extra join).

2. Nullable keys are a classic symptom of an Unnormalised database. Nulls in the database is bad news for performance; but Nulls in FKs means each table is doing too many things, has too many meanings, and results is very poor code. Good for people who like to "refactor" their databases; completely unnecessary for a Relational database.

3. Resolved: An Alert may be Acknowledged; An Acknowledgement may be Actioned.

4. The columns above the line are the Primary Key (refer Notation document). SensorNo is a sequential number within LocationId; refer Business Rules, it is meaningless outside a Location; the two columns together form the PK. When you are ready to INSERT a Sensor (after you have checked that the attempt is valid, etc), it is derived as follows. This excludes LoggerSensors, which are zero:

   INSERT Sensor VALUES (
       @LocationId,
       SensorNo = ( SELECT ISNULL(MAX(SensorNo), 0) + 1
           FROM Sensor
           WHERE LocationId = @LocationId
           )
       @SensorCode
       )

5. For accuracy or improved meaning, I have changed NetworkSlave monitors NetworkSensor to NetworkSlave collects Readings from NetworkSensor.

6. Check Constraints. The NetworkSensor and LoggerSensor are exclusive subtypes of Sensor, and their integrity can be set by CHECK constraints. Alerts, Acknowledgements and Actions are not subtypes, but their integrity is set by the same method, so I will list them together.

   • Every Relation in the Data Model is implemented as a CONSTRAINT in the child (or subtype) as FOREIGN KEY (child_FK_columns) REFERENCES Parent (PK_columns)

   • A Discriminator is required to identify which subtype a Sensor is. This is SensorNo = 0 for LoggerSensors; and non-zero for NetworkSensors.

   • The existence of NetworkSensors and LoggerSensors are constrained by the FK CONSTRAINTS to NetworkSlave and Logger, respectively; as well as to Sensor.
   • In NetworkSensor, include a CHECK constraint to ensure SensorNo is non-zero

   • In LoggerSensor, include a CHECK constraint to ensure SensorNo is zero

   • The existence of Acknowledgements and Actions are constrained by the identified FK CONSTRAINTS (An Acknowledgement cannot exist without an Alert; an Action cannot exist without an Acknowledgement). Conversely, an Alert with no Acknowledgement is in an unacknowledged state; an Alert with and Acknowledgementbut no Action is in an acknowledged but un-actioned state. .

7. Alerts. The concept in a design for this kind of (live monitoring and alert) application is many small programs, running independently; all using the database as the single version of the truth. Some programs insert rows (Readings, Alerts); other programs poll the db for existence of such rows (and send SMS messages, etc; or hand-held units pick up Alerts relevant to the unit only). In that sense, the db is a may be described as an message box (one program puts rows in, which another program reads and actions).

   The assumption is, Readings for Sensors are being recorded "live" by the NetworkSlave, and every minute or so, a new set of Readings is inserted. A background process executes periodically (every minute or whatever), this is the main "monitor" program, it will have many functions within its loop. One such function will be to monitor Readings and produce Alerts that have occurred since the last iteration (of the program loop).

   The following code segment will be executed within the loop, one for each AlertType. It is a classic Projection: -- Assume @LoopDateTime contains the DateTime of the last iteration INSERT Alert SELECT LocationId, SensorNo, ReadingDtm, "L" -- AlertType "Low" FROM Sensor s, Reading r WHERE s.LocationId = r.LocationId AND s.SensorNo = r.SensorNo AND r.ReadingDtm > @LoopDtm AND r.Value < s.LowerLimit INSERT Alert SELECT LocationId, SensorNo, ReadingDtm, "H" -- AlertType "High" FROM Sensor s, Reading r WHERE s.LocationId = r.LocationId AND s.SensorNo = r.SensorNo AND r.ReadingDtm > @LoopDtm AND r.Value > s.UpperLimit So an Alert is definitely a fact, that exists as a row in the database. Subsequently that may be Acknowledged by an User (another row/fact), and Actioned with an ActionType by an User.

   Other that this (the creation by Projection act), ie. the general and unvarying case, I would refer to Alert only as a row in Alert; a static object after creation.

8. Concerns re Changing Users. That is taken care of already, as follows. At the top of my (revised yesterday) Answer, I state that the major Identifying elements are static. I have re-sequenced the Business Rules to improve clarity.

   • For the reasons you mention, User.Name is not a good PK for User, although it remains an Alternate Key (Unique) and the one that is used for human interaction.

   • User.Name cannot be duplicated, there cannot be more than one Fred; there can be in terms of FirstName-LastName; two Fred Bloggs, but not in terms of User.Name. Our second Fred needs to choose another User.Name. Note the identified Indices.

   • UserId is the permanent record, and it is already the PK. Never delete User, it has historical significance. In fact the FK constraints will stop you (never use CASCADE in a real database, that is pure insanity). No need for code or triggers, etc.

   • Alternately (to delete Users who never did anything, and thus release User.Name for use) allow Delete as long as there are no FK violations (ie. UserId is not referenced in Download, Acknowledgement, Action).

   To ensure that only Users who are Current perform Actions, add an IsObsolete boolean in User (DM Updated), and check that column when that table is interrogated for any function (except reports) You can implement a View UserCurrent which returns only those Users.

   Same goes for Location and NetworkSlave. If you need to differentiate current vs historical, let me know, I will add IsObsolete to them as well.

   I don't know: you may purge the database of ancient Historical data periodically, delete rows that are (eg) over 10 years old. That has to be done from the bottom (tables) first, working up the Relations.

Feel free to ask Questions.

Note the IDEF1 Notation document has been expanded.