Say basically have two various kinds of sensors: one monitors analog current (for example on the temperature sensor) and something measures whether something is off or on (switch sensor).

I can not decide whether or not to have one table:

Id : PK
UpperLimit : FLOAT
UpperLimitAlertDelay : INT
LowerLimit : FLOAT
LowerLimitAlertDelay : INT
IsAnalog : BOOL

Id : PK
SensorId : FK
AnalogValue : FLOAT

OR separate everything out into separate tables:

Id : PK
UpperLimit : FLOAT
UpperLimitAlertDelay : INT
LowerLimit : FLOAT
LowerLimitAlertDelay : INT

Id : PK
AnalogSensorId : FK
Value : FLOAT

Id : PK
OnTooLongAlertDelay : INT

Id : PK
SwitchSensorId : FK

Right now I've it as being one table and that i make use of the "UpperLimitAlertDelay" because the "OnTooLongAlertDelay" if not utilizing it because the analog sensor.

Within the code I differentiate through the boolean flag around the Sensor table and make the right object (i.e. AnalogSensor or SwitchSensor) but I am wondering if it would be neater / more proper in the database level to split up it.

What guideline can you use with this type of decision? They're different organizations on a single level, but on another level you can appear at first sight both just sensors.

This really is frequently where I'm able to never decide what direction to consider when designing a database. Maybe whenever I personally use a bool to distinguish what fields mean / ought to be used, it will be another table?

General ideas about this subject or this sepcific problem appreciated.


EDIT: Some additional information.

The switch sensors monitor such things as whether a door is open, a fridge compressor is running, whether a product is switched on, etc.

Graphs and reviews could be produced on any sensor so they are utilised in the same manner it is simply the information will be either on/off or perhaps an analog value with respect to the type.

So essentially they can be treated exactly the same.

Within the blood pressure measurements table, it's always one row for just one reading through from ONE sensor.

To date the opinions appear to stop barking subjective - I suppose you will find just benefits and drawbacks to for both.

Does the data above change anybody's opinion?

Thanks! Mark.

Generally you would like very little redundancy in database design as you possibly can. Go lookup Normal Forms, anything below BCNF is generally difficult to maintain. Some programs use more redundancy to attain more performance for reads, but sacrifice clearness and write performance for this, for example data warehouses. Joins may be slow, but they're much better than sporadic data once the same details are saved two times.

I'd therefore advise to make use of the low one. Assume your sensors aren't linked any longer with perfect time-stamps: All of a sudden, the very first layout suggestion fails badly.

Is exactly the same application/database as your other question ?

By which situation, the solution continues to be provided for the reason that Data Model.

Whether it is not exactly the same application/db, or maybe this is not adequately clarified, please publish or comment. Eg. According to previous info, I modelled it to ensure that the SensorType table distinguishes Sensor (analogue or boolean) ... but we're able to:

  • differentiate it in the Sensor level,

  • or make Reading into subtypes: ReadingAnalog and ReadingSwitch. That may turn it into a little simpler for that programs that produce graphs, etc.

I would recommend to do something based on the normalisation rules.

Based on your requirements, you are able to select a no-sql database.