How far to go with database constraints?

This question is related to another question I asked. In my other question I ask peoples opinions about 3 different ways I could construct a database. The cleanest way I can think of doing it without (practically) repeating tables and strange notions such as "super tables" is option 2:

Location [Table]
- Id
- Name
- HasLogger
- LoggerRFID
- LoggerUpperLimit
- LoggerLowerLimit

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

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

LoggerReading [Table]
- LocationId [FK]
- Value

Alert [Table]
- Id [PK]

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

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

SensorAlertReading [Table]
- AlertId [FK]
- SensorReadingId [FK]

LoggerAlertReading [Table]
 - AlertId [FK]
 - LoggerReadingId [FK]

Now the problem with this option is that it allows readings from multiple sensors and multiple locations to be "linked" to a single alert.

To expand on why this is a problem, I will explain how the system works:

A location can contain many "live sensors", but only 1 logger. For this reason I put the logger attributes into the location table (it was effictively a 1 to 1 relationship). A logger collects readings until it is later collected, live sensors communicate readings immediately via a network and they have extra attributes like network slaves which have network address attributes.. so fairly different to loggers (I tried treating loggers as sensors at one point, didn't work out well).

When a sensor or logger goes out of range (indicated by the reading) the system generates an alert. The alert is for that sensor only and is considered active until a reading for that sensor (or logger) indicates that it is back in range. Until that time, readings that take the sensor further out of range are "linked" to that same alert.

So as you can see, a single alert should really only have readings for the same sensor linked to it, however my design above allows diffe开发者_C百科rent reading from different sensors and loggers to be associated with the same alert - should I be bothered that I haven't constrained that somehow? The other problem is that it allows alerts to exist without having any readings.

Hence my question; just how far should one go with constraints or bending a design to fit those constraints? I like the design above because it is simple - alerts can have sensor readings and logger readings, so it's a simple relation to link them.

I can't help thinking I'm missing a trick too - is there just a much better way to do this design? I've gone round in circles with it for ages now and there always seems to be a compromise (unless I repeat all the alert tables for the different reading types).

Thanks.

---

should I be bothered that I haven't constrained that somehow?

Yes.

You have made two basic mistakes.

1. Sticking Idiot keys on everything that moves.

   That has hindered your ability to model the data, as data (not as rows that have no meaning, but with an artificially enforced uniqueness), and expose Identifers; and Dependdencies (eg. a Sensor is Dependent o a Location). You are modelling spreadsheets, with pre-set Row_Ids, containing data. You need to Normalise the data, as data.

   This has resulted in the problem you have identified , but there are other problems as well.

   If you model the data, the Identifiers will be clear, and the Index and FK constraints will prevent this. What data is independent; what data belongs (is dependent on) what other data; what data does what to other data, and the basis of those actions.

   Then (the major issues having been addressed) you are left with only minor constraints to address minor areas.

2. Otherwise you are stuck with adding constraints all over the place to try and get what you want, but never quite getting there. You know you need them, so you are looking for them.

Wrong place. We need to back up to (1).

I have answered your other question, and included a ▶Sensor Data Model◀. That does not address the deficiencies you identify here. However, I just saw this question, I will update the DM tomorrow and include these tables and columns.

▶Link to IDEF1X Notation◀ for anyone who is unfamiliar with the Standard for modelling Relational databases.

Questions

1. It looks like you need a reference table for Sensors, the shelf item, to hold UpperLimit and LowerLimit; rather than repeating it for every Location. Or are they set, localised, for each Location.

2. Think about the Logger being SensorNo zero.

3. Why don't the Sensors have an RFID ?

4. At each Location, is the Logger optional, is it 1::0-1 ?,

---

Why not have:

Alert [Table]
- Id [PK]  
- SensorReadingId [FK]  
- LoggerReadingId [FK]  

And then you fill either the SensorReadingId or the LoggerReadingId. I suppose your structure is a simplified one, but often a table with nothing else then one PK is redundent.