Tables are usually split up to into logically distinct "things", so you don't have the same "things" twice. For example, you wouldn't want:
[SensorReadings]
Id : PK
UpperLimit : FLOAT
UpperLimitAlertDelay : INT
LowerLimit : FLOAT
LowerLimitAlertDelay : INT
IsAnalog : BOOL
AnalogValue : FLOAT
IsOn : BOOL
Because you're mixing the sensor and it's readings into one row. A sensor is a different thing than its readings:
[Sensors] [SensorReadings]
Id Id
UpperLimit SensorID
UpperLimitAlertDelay Reading
LowerLimit
LowerLimitAlertDelay
IsAnalog
Manufacturer
SerialNumber
LastInspectionDate
...
One thing i wouldn't so is split up "sensors" into two tables. A sensor is a sensor, that is the thing that it is. Like a customer is a customer, or a song is a song. You would have a table of songs, not a table of classical songs and another table for all the rest. If you do split up sensors into two tables, you can conceivable have two sensors with the same ID. Sensors are unique entities, they should be in the same table, and all have a unique ID.The fact that a sensor is analog or digital is the property of a sensor.
Your question is unique - your sensors can have Readings in different logical formats; some are analog floating point values, other are digital boolean values. You're struggling with how to store the "readings" of a sensor when not all sensor readings fit in the same logical column data type (i.e. float vs bool). It comes down to practicality, and what is best for the system.
You could store all the readings in a floating point number column:
[SensorReadings]
Id SensorID Reading
== ======== =======
1 3728 120.2
2 3728 120.3
3 89 1
4 89 0
5 3728 120.2
6 89 0
But now you have to know to interpret a floating point value of 0,1 as a logical on,off. Is that difficult to do? i don't personally think so. True, it's not making full use of the data types available in the database engine, but i don't really care. You're going to join SensorReadings with Sensors, so you'll have the IsAnalog column available to help you interpret. In other words:
SELECT Id, SensorID, Reading, Sensors.IsAnalog
FROM SensorReadings sr
INNER JOIN Sensors s ON sr.SensorID = s.SensorID
Will give you a pretty easy to parse results set:
Id SensorID Reading IsAnalog
== ======== ======= ========
1 3728 120.2 false
2 3728 120.3 false
3 89 1 true
4 89 0 true
5 3728 120.2 false
6 89 0 true
You could even create a helper view (or just a query), that decodes the reading as AnalogReading and DigitalReading:
CREATE VIEW SimpleSensorReadings AS
SELECT Id, SensorID, Reading AS RawReading,
CASE Sensors.IsAnalog
WHEN 0 THEN Reading
ELSE NULL
END AS AnalogReading,
CASE Sensors.IsAnalog
WHEN 1 THEN CAST(Reading AS BOOL)
ELSE NULL
END AS DigitalReading,
Sensors.IsAnalog
FROM SensorReadings sr
INNER JOIN Sensors s ON sr.SensorID = s.SensorID
This would give you:
[SimpleSensorReadings]
Id SensorID RawReading AnalogReading DigitalReading IsAnalog
== ======== ========== ============= ============== ========
1 3728 120.2 120.2 true
2 3728 120.3 120.3 true
3 89 1 true false
4 89 0 false false
5 3728 120.2 120.2 true
6 89 0 false false
It depends who has to deal with the results. i can easily imagine code first checking the "IsAnalog" column, then reading out either the AnalogReading or DigitalReading as appropriate.
You could do what you originally suggested; split them up into multiple tables. But now the problem becomes: How do you access the data? It seems to me, if i were having this system of sensor readings, at some point i'm going to have to do something with them - show them to a user. Now i'd have to jump through hoops to rejoin the data:
SELECT ID, AnalogSensorID AS SensorID,
Value AS RawReading, Value AS AnalogReading,
true AS IsAnalog
FROM AnalogSensorReadings
UNION ALL
SELECT ID, SwitchSensorID AS SensorID,
CAST(IsOn AS float) AS RawReading, null AS AnalogReading, IsOn AS DigitalReading,
false AS IsAnalog
giving you
Id SensorID RawReading AnalogReading DigitalReading IsAnalog
== ======== ========== ============= ============== ========
1 3728 120.2 120.2 true
2 3728 120.3 120.3 true
1 89 1 true false
2 89 0 false false
3 3728 120.2 120.2 true
3 89 0 false false
Except now the "Id" is also hard to decode, because two different readings can have the same "ID". A reading is a reading, and should be unique.
A compromise you are probably looking for, is what you originally had.
[SensorReadings]
Id SensorID AnalogReading DigitalReading
== ======== ============= ==============
1 3728 120.2
2 3728 120.3
3 89 true
4 89 false
5 3728 120.2
6 89 false
Yes this leaves you with a lot of (null) values - but the expense of joining tables back together is a practical problem that has to factor into your design decision.
i think of it like the registry in Windows. A key contains a value. You don't really care how that value is stored, as long as you can read it, as the type is logically is. To accomplish that in a database i would use multiple data-type columns, and read them as appropriate.