Database design problem.

Question

Hi, I'm working on a database design for a building monitoring system. It goes a bit like this: There's a building. A building has multiple areas which can contain loggers or groups of live feed sensors. A logger has a single sensor and it's data is collected by handsets which is then downloaded into the system later.

So for example:

Building:
    + Area1:
          Cold room 1 (Logger)
          Cold room 2 (Logger)
          + Freezer 1 (Live monitoring):
                Live sensor 1
                Live sensor 2

A logger has readings that are stored in the database, as do the live monitoring sensors, and the readings can generate alerts.

My problem is that a logger and a live monitoring sensor are very similar, but because they exist at different levels of the hierachy I'm finding it difficult to model in a way that seems nice. Here is what I have come up with so far. This is just a mock up to play with ideas, theres plenty missing:

http://thejunkroom.co.uk/~marks/db1.png

Bit of mess I know..

It's a shame it can't be like this:

Building:
    + Area1:
          + Foo
                Cold room 1 (Logger)
                Cold room 2 (Logger)
          + Freezer 1 (Live monitoring):
                Live sensor 1
                Live sensor 2

As then it could be more like this:

http://thejunkroom.co.uk/~marks/db2.png

But alas it isn't this way.

So, is there a better design for this?

I hope this makes some kind of sense..

Thanks, Mark.

Answer 1

How abnout something like this?

Second structure with subclass relationship...

Building
  BuildingId   pk
  BuildingName
  etc

Area
  AreaId      pk
  AreaName
  BuildIngId  fk -> Building  
  etc

Location
  LocationId         pk
  LocationType (LiveMonitor, Logger, Handprobe)  pk
  LocationName
  AreaId       fk -> Area
  etc

LiveMonitorLocation
  LocationId  pk, fk -> Location
  LocationType ConstantValue = LiveMonitor  fk ->  Location    

LoggerLocation
  LocationId   pk, fk -> Location
  LocationType ConstantValue = Logger  fk ->  Location    


HandprobeLocation
  LocationId   pk, fk -> Location
  LocationType ConstantValue = Handprobe fk ->  Location    

Logger
  LoggerId     pk
  LocationId   fk -> LoggerLocation
  SensorId     fk -> Sensor

Handprobe
  HandProbeId  pk
  Locationid  fk -> HandprobeLocation

Sensor
  SensorId     pk

LiveMonitorSensors
  SensorId     pk, fk -> Sensor
  LocationId   pk, fk -> LiveMonitorLocation

SensorReadings
  SensorId     pk, fk -> Sensor
  ReadingUtc   pk
  ReadingValue data