The best database design for the "sensory system"

Question

I do school work and ...

I need to make a vehicle tracking system. I was thinking about these three projects. What do you think?

My database schemas

opinions?

Answer 1

• If you always measure and save all parameters during one measurement session, go to design 1 .

  Moving attributes to separate tables makes sense if attributes are rarely stored and / or rarely needed.

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

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

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

• Moving liquid to a separate table (as in project 2 ) makes sense if the list of fluids you use is unknown during development (i.e. some third fluid, such as hydrogen or helium-3 or whatever they invent, will be used vehicles, and you will need to track it without redesigning the database).

  Of course, this is not a likely scenario.

Answer 2

if you read from sensors at the same time, the second design seems redundant to me. It would be wise to store information separately only if you read this information at different times.

I would suggest the first design.

Answer 3

Your application must deal with two types of things.

• Sensors = what type, where in the engine and even parameters, such as polling frequency, etc.
• Reads = individual records with time stamps from one (or several?) Sensors.

There are a few things to consider:
- How can we find ways to abstract the concept of the sensor ? The idea is that we could then identify and process sensor instances through their properties, rather than know where they are in the database.
- It is best to store all measurements for a given timestamp in a single “Read” record or have one record per sensor for reading, even if the set includes several measurements.

A quick answer to the last question is that one-way read-write seems to be more flexible ; in the same way, we can process both groups of dimensions that are systematically polled at the same time, and other dimensions that are asynchronous to the first. Even if right now, all measurements come at once, the ability to simply add sensors without changing the database schema and to process them in the same mode is attractive.

Perhaps the following design will 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 will have several tables where "enumerations" for various types of sensors will be defined, and binding to the application logic will be carried out using mnemonic "keys", 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 will not stop you from also calling sensors by their identifier and grouping the readings in the same line of results. 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