Re: Database design for scientific tests

[Martin Karm]

Once again, a click in my head!

The 'look-up table' works very nicely for the initial testing conditions, and makes a lot of sense. The (simplified) new tables look something like:

CREATE TABLE initial_conditions (
initial_conditions_id INT NOT NULL ,
conditions VARCHAR(45) NULL,
value VARCHAR(45) NULL,
PRIMARY KEY (initial_conditions_id)
);

CREATE TABLE initial_test_conditions(
initial_test_conditions_id INT NOT NULL,
test_id INT NOT NULL ,
initial_conditions_id INT NULL ,
PRIMARY KEY (initial_test_conditions_id) ,
INDEX fk_initial_test_conditions_initial_conditions (initial_conditions_id ASC) ,
INDEX fk_initial_test_conditions_test (test_id ASC) ,
CONSTRAINT fk_initial_test_conditions_initial_conditions
FOREIGN KEY (initial_conditions_id)
REFERENCES initial_conditions(initial_conditions_id) ,
CONSTRAINT fk_initial_test_conditions_test
FOREIGN KEY (test_id)
REFERENCES test(test_id)
);




However, I'm having a bit of a tough time applying the look-up table concept to the test_data table though. Here is an example of two of the tables I'm trying to model:


//Initial conditions here, time, date, etc

..................Pressure.1..|..2..|..3
_______________________________________
Sample.. Luminosity.. Temperature
_______________________________________
1.......12 ...................99...98...97
2.......13 ...................88...72...37
3.......14 ...................76...54...21

Another example table

Sample... Weight... Resistivity... Saturation
_________________________________________________
1..............12.............99.............98
................................100............21
..................................85............44
_________________________________________________
2...............13..............88............72
..................................21............10

(Sorry, terrible tables,I know)

Sample is taken care of through the FK to the Tested_Sample table. A look-up table takes care of the "singles" (weight, luminosity), but the multiple pressure vs temperature from the first example is confusing me. Same confusion for multiple saturation/resistivity.

Any thoughts?

Again, thank you very much for your help! It is VERY much appreciated!