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.