Best database design for a "sensor system"

Question

I'm doing a schoolwork and..

I have to do a vehicle tracker system. I thought of these three designs. What do you think?

My database schemas (png at ImageShack ~99KB) http://img390.imageshack.us/img390/3781/des.png

Opinions?

Answer 1

• If you always measure and store all parameters within one measuring session, then go for the design 1.

  Moving the attributes into separate tables only makes sense if the attributes are rarely stored and/or rarely needed.

• If you have separate sensors for position and temperature, then go for design 3.

  This is most probable, since the position is measured by a GPS tracker and temperature and oil level are measured by the vehicle sensors, which are separate devices and the measurements are performed at separate times.

  You may even need to add a separate table for each sensor (i. e. if different sensors measure gas and temperature at different times, then make two tables for them).

• Moving liquid into a separate table (as in design 2) makes sense if the list of the liquids you use is not known in design time (i. e. some third liquid, like hydrogen or helium-3 or whatever they will invent will be used by the vehicles and you will need to track it without redesigning the database).

  This is not a likely scenario, of course.

Answer 2

Hi Nick,

if you're reading from the sensors at the same time the second design looks like an overkill to me. It would make sense to keep information separate just if you read that information at different times.

I would suggest the first design.

Answer 3

Your application needs to deal with two types of things

• Sensors = which type, where in the engine, and even parameters such as polling frequency and such..
• Reads = individual time-stamped recordings from one (or several ?) sensors.

There are a few things to think about:
- How can we find ways of abstracting the sensor concept? The idea is that we could then identify and deal with sensor instances through their properties, rather than having to know where to they are found in database.
-Is is best to keep all measurements for a given timestamp in a single "Read" record or to have one record per sensor, per read, even if several measurements come in sets.

A quick answer to the last question is that the single read event per record seems more flexible; we'll be able to handle, in the very same fashion, both groups of measurements that are systematically polled at the same time, and other measurements that are asynchonous to the former. Even if right-now, all measurements come at once, the potential for easy addition of sensors without changing the database schema and for handling them in like-fashion, is appealing.

Maybe the following design would be closer to what you need:

tblSensors
     SensorId   PK
     Name       clear text description of the sensor  ("Oil Temp.")
     LongName   longer description   ("Oil Temperarure, Sensor TH-B14 in crankshaft")
     SensorType enumeration  ("TEMP", "PRESSURE", "VELOCITY"...)
     SensorSubType   enumeration   ("OIL", "AIR"...)
     Location   enumeration  ("ENGINE", "GENERAL", "EXHAUST"...)
     OtherCrit  other crietrias which may be used to identify/seach for the sensor.


tblReads
     Readid   PK
     DateTime   
     SensorId   FK to tblSensors
     Measurment  INTeger value
     Measurement2    optional extra meassurement (maybe to handle say, all
                     of a GPS sensor read as one "value"
     Measurement3 ... also may have multiple columns for different types of 
              variables (real-valued ?)

In addition to the above you'd have a few tables where the "enumerations" for the various types of sensors are defined, and the tie-in to the application logic would be by way of the mnemonic-like "keys" of these enumerations. eg.

SELECT S.Name, R.DateTime, R.Measurement
FROM tblReads R
JOIN tblSensors S ON S.SensorId = R.SensorID
WHERE S.SensorType IN ('Temp', 'Pres')
  AND S.Location = "ENG"
  AND R.DateTime > '04/07/2009'
ORDER BY R.DateTime

This would not prevent you to also call the sensors by their id, and to group reads on the same results line. eg.

SELECT R1.DateTime, R1.Measurement AS OilTemp, R2.Measurement AS OilPress,
       R3.Measurement AS MotorRpms
FROM  tblReads R1
LEFT OUTER JOIN tblReads R2  ON R1.DateTime = R2.DateTime
LEFT OUTER JOIN tblReads R3  ON R1.DateTime = R3.DateTime
WHERE R1.SensorId = 17 
  AND R2.SensorId = 11 
  AND R3.SensorId = 44 
  AND R1.DateTime > '04/07/2009' AND R1.DateTime < '04/08/2009'
ORDER BY R3.Measurement DESC -- Sorte by Speed, fastest first