Database: when to split into separate tables?

Question

Say if I have two different types of sensors: one monitors analog voltage (such as on a temperature sensor) and one measures whether something is on or off (switch sensor).

I can't decide whether to have one table:

[Sensor]
Id : PK
UpperLimit : FLOAT
UpperLimitAlertDelay : INT
LowerLimit : FLOAT
LowerLimitAlertDelay : INT
IsAnalog : BOOL

[SensorReading]
Id : PK
SensorId : FK
AnalogValue : FLOAT
IsOn : BOOL

OR separate it all out into separate tables:

[AnalogSensor]
Id : PK
UpperLimit : FLOAT
UpperLimitAlertDelay : INT
LowerLimit : FLOAT
LowerLimitAlertDelay : INT

[AnalogSensorReadings]
Id : PK
AnalogSensorId : FK
Value : FLOAT

[SwitchSensor]
Id : PK
OnTooLongAlertDelay : INT

[SwitchSensorReadings]
Id : PK
SwitchSensorId : FK
IsOn : BOOL

At the moment I have it as one table and I use the "UpperLimitAlertDelay" as the "OnTooLongAlertDelay" when not using it as the analog sensor.

In the code I differentiate by the boolean flag on the Sensor table and create the appropriate object (i.e. AnalogSensor or SwitchSensor) but I'm wondering if it'd be neater / more proper at the database level to separate it out.

What rule of thumb would you use for this kind of decision? They are different entities on one level, but on another level you could say they are both just sensors.

This is often where I can never decide what direction to take when creating a database. Maybe whenever I use a bool to differentiate what fields mean / should be used, it should really be a separate table?

General thoughts on this topic or this sepcific problem appreciated.

Thanks!

EDIT: Some further information.

The switch sensors monitor things like whether a door is open, a fridge compressor is running, whether an appliance is turned on, etc.

Graphs and reports can be generated on any sensor so they are used in the same way; it's just the data will either be on/off or an analog value depending on the type.

So basically they are generally treated the same.

In the readings table, it is always one row for ONE reading off of ONE sensor.

So far the opinions seem to be quiet subjective - I guess there are just pros and cons to both ways.

Does the information above change anybody's opinion?

Thanks! Mark.

Answer 1

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

In which case, the answer has been supplied in that Data Model.

If it isn't the same app/db, or if this question has not been adequately answered, pls post or comment. Eg. Based on previous info, I modelled it so that the SensorType table differentiates Sensor (analogue or boolean) ... but we could:

• differentiate it at the Sensor level,

• or make Reading into subtypes: ReadingAnalog and ReadingSwitch. That could make it a little easier for the programs that produce graphs, etc.