2

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

Community
  • 1
  • 1
Mark
  • 1,296
  • 13
  • 28

2 Answers2

4

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 ?,

PerformanceDBA
  • 32,198
  • 10
  • 64
  • 90
  • Thanks for your answer - I've read your other answer too and left a comment about not being able to see the model. I'll wait until I've seen that before I respond fully. To answer one of your questions (3) - Sensors do not have RFIDs (Radio Frequency Id) because they are linked to the system in a different way. I'll continue in a new comment. – Mark Dec 05 '10 at 22:04
  • Basically their are two ways of monitoring a location; Retrospectivelly through a self contained battery powered data logger, or live via a network system. The network system consists of a Network Monitoring Unit and Network Slaves. The Network Slaves are linked to the Network Monitoring Unit via mains signalling. The network slaves can each have two Sensors attached (though that will be more soon). A location can have many of these sensors to allow live monitoring of Air, Water, Humidity, etc. – Mark Dec 05 '10 at 22:09
  • The Sensors that plug into a single Network Slaves via cables can go off to different locations, so Network Slaves are not constrained to locations, so instead of a locations "containing" a Network Slave, it contains a set of Sensors. Hope that clarifies things a bit. – Mark Dec 05 '10 at 22:15
  • Yep the logger is optional; 1::0-1. – Mark Dec 05 '10 at 22:54
  • 1
    @Mark: Thanks, and it certainly was a pleasure working with someone who knew their data, and genuinely wanted a good database. – PerformanceDBA Dec 07 '10 at 21:55
-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.

iDevlop
  • 24,841
  • 11
  • 90
  • 149
  • 1
    I've never felt comfortable with NULL foreign keys. Whenever I read up on database theory, it says they are bad and the design is broken, so I keep thinking maybe I am just missing something. – Mark Dec 02 '10 at 11:12
  • 1
    Also, this does not allow many readings per Alert (unless you mean leave the link tables in place too, but that only solves the problem of needing at least one reading per alert).. – Mark Dec 02 '10 at 11:24
  • Mark, I really do not think null FK are that bad. http://bytes.com/topic/sql-server/answers/81196-null-foreign-key – iDevlop Dec 02 '10 at 16:40
  • 1
    @iDevelop. Null FKs are hideous, get rid of them. It means you have made basic errors during the modelling, and almost always means it is not normalised. Anyway, they fail DRI, so you cannot use them unless you remove the DRI; in which case you have a data heap, not a database. – PerformanceDBA Dec 05 '10 at 15:34
  • @PerformanceDBA: a Customer record for which you do not know the Category. CategoryId is null, and there is no normalisation problem in there AFAIK. – iDevlop Dec 05 '10 at 15:45
  • 1
    @iDevlop. I suppose it boils down to loose data heaps vs tight data bases; and you seem not to understand business rules implemented in the db. If the CategorId (FK) for a Customer row is not known, it should not be inserted; and would not be in a db. Otherwise you have failed joins, and missing rows all over the data heap. – PerformanceDBA Dec 06 '10 at 00:15