Re: Database design for scientific tests

[Al Lombardo]

Here's a simple model that, I think, encompasses what you need:

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `mydb`;

-- -----------------------------------------------------
-- Table `mydb`.`sample`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`sample` (
`sample_id` INT NOT NULL AUTO_INCREMENT ,
`sample_information` VARCHAR(45) NULL ,
PRIMARY KEY (`sample_id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`test`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`test` (
`test_id` INT NOT NULL AUTO_INCREMENT ,
`test_name` VARCHAR(45) NULL ,
`other_test_info` VARCHAR(45) NULL ,
PRIMARY KEY (`test_id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`tested_sample`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`tested_sample` (
`sample_id` INT NOT NULL ,
`test_id` INT NOT NULL ,
`test_date` VARCHAR(45) NULL ,
PRIMARY KEY (`sample_id`, `test_id`) ,
INDEX fk_tested_sample_sample (`sample_id` ASC) ,
INDEX fk_tested_sample_test (`test_id` ASC) ,
CONSTRAINT `fk_tested_sample_sample`
FOREIGN KEY (`sample_id` )
REFERENCES `mydb`.`sample` (`sample_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_tested_sample_test`
FOREIGN KEY (`test_id` )
REFERENCES `mydb`.`test` (`test_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`test_data`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`test_data` (
`test_data_id` INT NOT NULL AUTO_INCREMENT ,
`tested_sample_sample_id` INT NOT NULL ,
`tested_sample_test_id` INT NOT NULL ,
`test_date_type` VARCHAR(45) NULL ,
`test_data_value` VARCHAR(45) NULL ,
PRIMARY KEY (`test_data_id`, `tested_sample_sample_id`, `tested_sample_test_id`) ,
INDEX fk_test_data_tested_sample (`tested_sample_sample_id` ASC, `tested_sample_test_id` ASC) ,
CONSTRAINT `fk_test_data_tested_sample`
FOREIGN KEY (`tested_sample_sample_id` , `tested_sample_test_id` )
REFERENCES `mydb`.`tested_sample` (`sample_id` , `test_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

You'll need to add the appropriate attributes to all the tables. Such as the sample table will need all the data about the sample ( color, weight etc.). Also, if a test can be re-performed, I'd add the test_date (defined as a datetime stamp) as part of the key fields.

Hope this helps.