Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004
    Posts
    2

    One or two tables? - A Design Issue

    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...
    Attached Thumbnails Attached Thumbnails schema.gif  

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    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.

  3. #3
    Join Date
    Oct 2003
    Posts
    706
    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.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  4. #4
    Join Date
    Aug 2004
    Posts
    2

    Smile

    Quote Originally Posted by andrewst
    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.
    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.

    Quote Originally Posted by andrewst
    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.
    This could also work but makes things more complicated (see above).

    Thanks a lot!

  5. #5
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249

    Agree with sundialsvcs

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •