Hi,

Actually I'm developing a database which should store some values from various hardware-tests (sensors, connectors, etc.). The problem is, that there are two variants from one sensor which actually makes only one test out of 5 different. So I was thinking of the design you see in the attachement.
The good thing in this design is that integrity-checks will work (just cascade). The bad thing is that there might be one entry in both measurement-tables, which is actually not what I want. The other solution is to make a table for every sensor-type.

So, what should I do or is there even a better solution I don't see?

Thank you in advance...

The design is OK, and commonly used. To ensure that each instance appears on only one of the subtypes you could do this:
- create a unique constraint on sensor1(sensorID,type) - in addition to the primary key on (sensorID).
- add type to the 2 subtype tables
- make the foreign keys on the subtypes reference the unique key
- add a check constraint to each subtype table like CHECK (type = 'typeA')

However, this doesn't prevent the creation of a sensor1 row with no corresponding detail in either of the subtype tables! If your DBMS supports "assertions" (multi-table check constraints) then you may be able to do this.

The alternative, less pure but often quite pragmatic, is to dispense with the subtype tables altogether and just have a single table, using check constraints to ensure that only the appropriate columns for the type are populated on each row.

I respectfully dissent. You have a sensor and that sensor produces discrete units of data called measurements. One of the attributes of a measurement data-point is its "measurement_type."

I would not advocate the use of two separate tables, absent any other motivating factor.

This is no problem since at the beginning there are only entries in the sensor-table and then the people are doing the measurements and feed the database with the results.

This could also work but makes things more complicated (see above).

Thanks a lot!

Hi,

I completely agree with sundialsvcs (and not so respectfully) when he states that:

You have a sensor and that sensor produces discrete units of data called measurements. One of the attributes of a measurement data-point is its "measurement_type."

What happens when a third measurement type is added? Create a new table? Then what when there are a fourth, and a fifth, and so on, measurement types?

Using a single "measurement" table that has a measurement_type handles this issue gracefully. As an additional benefit, you will end up writing a lot less code, since you will have to write once, only once, any code that handles different types of measurements.


Ravi