(I'm particularly interested in answers that view the question below as a special case of the question: how should a RDBMS enforce structural constraints that are more specific than "one-to-many" and "many-to-many"?)
Much experimental data in biomedical research is collected in "plates" of rectangularly arrayed "wells". These well array plates are commercially available a few standardized sizes: 2 × 3, 4 × 6, 8 × 12, 16 × 24, and 32 × 48.
Consider the following two alternatives for storing measurements from wells of 2 × 3 plates in an RDB:
-- alternative 1 CREATE TABLE measurement_foo ( plate_id FOREIGN KEY REFERENCES plate(plate_id), plate_row CHAR(1), plate_column INTEGER, value REAL ); -- alternative 2 CREATE TABLE measurement_foo ( plate_id FOREIGN KEY REFERENCES plate(plate_id), a1 REAL, a2 REAL, a3 REAL, b1 REAL, b2 REAL, b3 REAL );
My instinct is to go with alternative 1: it generalizes to plates of any size, and it can be modified in a straightforward way to record multiple different measurements per well, as in
CREATE TABLE measurement ( plate_id FOREIGN KEY REFERENCES plate(plate_id), plate_row CHAR(1), plate_column INTEGER, foo FLOAT, bar FLOAT, baz FLOAT );
In contrast, to adapt alternative 2 to the 16 × 24-well format (for example), would entail defining a table with 1 + 384 columns:
b01, ... ,
p24. Moreover, a different such table would need to be defined for each type of measurement.
My main reason for even considering alternative 2 is that it alone enforces the constraint that each plate contains exactly 6 wells; alternative 1 does not.
(Also, one record of the table in alternative 2 would hold as much information as up to 6 records of the one in alternative 1, which makes me wonder whether the table in alternative 1 is fully normalized.)
For alternative 1, I can imagine ways to enforce the weaker constraint that a plate contains at most 6 wells, namely, by imposing a uniqueness constraint on (plate_id, plate_row, plate_column), and restricting columns plate_row and plate_column to the domains ('a', 'b') and (1, 2, 3), respectively. But it is still possible for wells to be missing from the data.
How should the database enforce the structural relationship between plates and wells?