Re: Database design/normal form conundrum
Date: 27 Mar 2004 14:30:30 -0800
Message-ID: <a264e7ea.0403271430.b05c416_at_posting.google.com>
>> 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 - 23:30:30 CET