MySQL Forums
Forum List  »  MySQL Workbench

Re: Database design for scientific tests
Posted by: Al Lombardo
Date: June 24, 2009 08:27AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Database design for scientific tests
1854
June 24, 2009 08:27AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.