Re: Database design/normal form conundrum

>> A database exists whereby each record [sic] contains a data value.
The data
value has exactly five tests applied to it. A test consists of a pass/fail result, a user comment about the failure, and a user decision as to whether or not to ignore the error. So, that's five tests, consisting of 3 fields [sic] apiece, making 15 fields [sic] in all; <<

Rows are not records; fields are not columns; tables are not files. Try this approach.

CREATE TABLE Tests
(sample_id INTEGER NOT NULL PRIMARY KEY,  ...
 );

CREATE TABLE Results
(sample_id INTEGER NOT NULL

     REFERENCES Tests(test_id),
 test_nbr INTEGER NOT NULL

     CHECK (test_nbr BETWEEN 1 AND 5),
 PRIMARY KEY (test_id, test_nbr),
 results CHAR(1) DEFAULT 'F' NOT NULL

     CHECK(results IN ('F', 'P')),
 decision CHAR(1) DEFAULT 'A' NOT NULL

     CHECK(decision IN ('A', 'I')),
 comment VARCHAR(500) NOT NULL);

Alter the check constraint if you need to allow more tests. Write a little code to make sure you have all five results. Received on Sat Mar 27 2004 - 16:30:30 CST