Database “supertable” vs more tables vs generic ta

Question

I'm trying to decide on a database design. More specifically, this is a sub-part of a larger design. Basically, there are "Locations" - each location can have any number of sensors associated with it, and it can have a logger (but only 1).

I have sensor readings and I have logger readings, each different enough I think to warrant separate tables.

If a sensor reading goes out of range, an alert is generated. While a sensor reading stays out of range, they keep being associated with that alert so you end up with 1 alert containing many readings allowing me to graph the alert later so I can spot trends, etc.

Same with logger readings.

Here are my 3 ideas so far for storing this data:

Option 1:

Location [Table]
- Id [PK]
- Name
- HasLogger

LiveSensor [Table]
- LocationId [FK]
- Id [PK]

LiveSensorReading [Table]
- Id [PK]
- SensorId [FK]
- Value

LiveSensorAlert [Table]
- Id [PK]
- SensorReadingId [FK] (may not be needed - enforces need to always have at least 1 reading)

LiveSensorAlertCorrectiveAction [Table]
- LiveSensorAlertId [FK]
- CorrectiveActionId [FK]
- ByUserId [FK]

LiveSensorAlertAcknowledgement [Table]
- LiveSensorAlertId [FK]
- ByUserID [FK]

LiveSensorAlertReading [Table]
- SensorAlertId [FK]
- SensorReadingId [FK]

LoggerReading [Table]
- LocationId [FK]
- Value

LoggerAlert [Table]
- Id [PK]
- LoggerReadingId [FK] (may not be needed - enforces need to always have at least 1 reading)

LoggerAlertReading [Table]
- LoggerAlertId [FK]
- LoggerReadingId [FK]

LoggerAlertCorrectiveAction [Table]
- LoggerAlertId [FK]
- CorrectiveActionId [FK]
- ByUserId [FK]

LoggerAlertAcknowledgement [Table]
 - LoggerAlertId [FK]
 - ByUserID [FK]

• Problem: Lots of repeated tables (does that really matter though??)

Option 2:

Location [Table]
- Id
- Name
- HasLogger

Sensor [Table]
- Id [PK]
- LocationId [FK]

SensorReading [Table]
- Id [PK]
- SensorId [FK]
- Value

LoggerReading
- LocationId [FK]
- Value

Alert [Table]
- Id [PK]

AlertCorrectiveAction [Table]
- AlertId [FK]
- CorrectiveActionId [FK]
- ByUserId [FK]

AlertAcknowledgement [Table]
- AlertId [FK]
- ByUserId [FK]

SensorAlertReading
- AlertId [FK]
- SensorReadingId [FK]

LoggerAlertReading
 - AlertId [FK]
 - LoggerReadingId [FK]

• Problem: Does not enforce "at least 1 reading per alert" rule.
• Problem: Allows more than one type of reading to reference the same alert.

Option 3:

Location [Table]
- Id
- Name
- HasLogger

Sensor [Table]
- Id [PK]
- LocationId [FK]

SensorReading [Table]
- Id [PK]
- SensorId [FK]
- Value

LoggerReading
- LocationId [FK]
- Value

Alert [Table] "super table"
- Id [PK]

LoggerAlert [Table]
- AlertId [PK, FK]
- LoggerReadingId [FK]

SensorAlert [Table]
- AlertId [PK, FK]
- SensorReadingId [FK]

AlertCorrectiveAction [Table]
- AlertId [FK]
- CorrectiveActionId [FK]
- ByUserId [FK]

AlertAcknowledgement [Table]
- AlertId [FK]
- ByUserId [FK]

SensorAlertReading [Table]
- SensorAlertId [FK]
- SensorReadingId [FK]

LoggerAlertReading [Table]
 - LoggerAlertId [FK]
 - LoggerReadingId [FK]

• Problem: Nothing stopping a LoggerAlert and SensorAlert referencing same Alert (same problem as option 2).
• Problem: Obfuscates database (isn't super table more of an OO concept? A database is meant to be purely relational isn't it?)

I think so far I'm prefering option 1 because it just seems so clean and the intent is clear (I hope!), even though I'm repeating tables effectively.

The only slight problem I've just thought of is that readings for different sensors could still become associated with the one alarm.

I'm wondering what peoples opinions are on the above options. I've seen using "super tables" recommended quiet often but for some reason it just doesn't feel right - it almost feels like a bit of a hack especially when I see the methods for trying to ensure data integrity. It seems more akin towards OO programming than relational design.

Thanks.

EDIT: Some further info to help answer some of the questions below:

Most of the time the database is only manipulated through an application server, if that makes any difference.

The live alerts and logger alerts are generally treated the same, so I'm probably going to be dealing with all alerts most of the time, rather than dealing with logger alerts and live alerts in different ways.

The logger has fairly specific columns that live in the location table. Since the location and logger would be a 1 to 1 mapping I decided against having a separate logger table and so far it seems to of worked out fine and kept it simple. Example columns: LoggerRFID (int), LoggerUpperLimit (float), LoggerLowerLimit (float), etc. You could almost argue that a logger is a sensor, but I went down that road and it didn't turn out too well.

I can almost accept making the alerts generic, but as one of the answers said I'm trying to be very sure about this so continuing the research for as long as I can before choosing a specific path.

Answer 1

Some thoughts (ideas and opinions, not answers) on this:

The "supertable" (type/subtype) model is compelling, but can be tricky to implement and support. A few tricks:

ALERT
  AlertId    PK 1/2
  AlertType  PK 2/2  Check constraint (1 or 2, or better L or S)

...that is, compound primary key, where "type" must always be L)og or S)ensor.

LOGALERT
  LogAlertId  PK 1/2  FK 1/2
  AlertType   PK 2/2  FK 2/2

(and again for SENSORALERT)

...that is, same compound primary key, and the foreign key is on both columns. Done this way, there can only be one subtype table for a given type table, and the top table clearly shows which subtype is involved. No way to enforce the existance of a row in the subtype table, so set up your data carefully. And much of the querying complexity can be dealt with (covered up?) using views.

The downside is, it is complex, confusing to those not (yet) familiar with it, and will require extra support and effort. The real question is, is it worth it?

• How often must you deal with all alerts, not only Log or only Sensor? If most of the time you only have to deal with one or the other, it's probably not worth it.
• How much Log- or Sensor-specific details do you have to deal with? Beyond the actual events related to an individual alert, how similar across both types are the myriad attributes (details in columns) you'll be tracking? If users, aknowledgements, and corrective actions are (sufficiently) identicial, you can make them attributes (columns) of ALERT, but if not then you have to make them atttributes of the appropriate subtype, and you lose the consolidating advantage of the supertype.
• And you have to get it correct now, during design time. Research, ask questions, gaze into crystal balls (i.e. ponder what might happen in the future to invalidate everyone's current assumptions), because if you get it wrong now you and your successors may have to live with it forever.

Answer 2

I think this question has been answered in your other question, with the full Data Model; otherwise (if there is anything outstanding), please post an Edit to this Question.

If you are interested in the Supertype-Subtype Relational structure, in a general sense, this question may be of interest to you.

May I suggest you close this question.

Answer 3

You could add an ObjectType column to your mirrored tables in option one, and provide values of either Sensor or Logger. Your database design would then look something like this:

Location [Table]
- Id
- Name
- HasLogger

ObjectType [Table]
- Id [PK]
- Name -- either Sensor or Logger
- Description

Object [Table]
- Id [PK]
- LocationId [FK]
- ObjectTypeId [FK]

Reading [Table]
- Id [PK]
- ObjectId [FK]
- Value

ObjectReading
- ObjectId [FK]
- ReadingId [FK]

Alert [Table]
- Id [PK]
- ReadingId [FK]

AlertCorrectiveAction [Table]
- AlertId [FK]
- CorrectiveActionId [FK]
- ByUserId [FK]

AlertAcknowledgement [Table]
- AlertId [FK]
- ByUserId [FK]

This design does obfuscate the underlying purpose of the database a bit, largely because I couldn't think of a better word to describe "sensor or logger" than "object" -- if there's some specific term that could collectively describe something attached at a location, that would certainly facilitate understanding the database.

You could also remove the Id column from ObjectType and make the Name a primary key if you aren't particularly squeamish about non-integer IDs in tables. I've had bad experiences with tables like ObjectType where the primary key isn't an integer, though, so I almost always use one.

I also agree with KM's assessment above that each table's primary key ID should be named something longer than "Id".