-- updated 040423 DDH

-- Trait_study: traitscore->traitstudytraitscore->traitstudy
-- Score_Mean_SD: removed
-- Germplasm_Mean_SD: removed
-- Germplasm_score: removed

create table traitscore (
	id integer auto_increment primary key,
	name varchar(255),
	-- Germplasm
	germplasmid integer,
	-- Score
	score float,
	-- Standard_deviation
	standarddeviation float,
	-- Replications
	replications integer,
	-- Percent_of_local_check
	percentoflocalcheck float,
	-- Difference_from_local_check
	differencefromlocalcheck float,
	-- Unit
	unit varchar(255)
) ENGINE=InnoDB;
create index name_index on traitscore (name);
create index germplasmid_index on traitscore (germplasmid);

create table traitscoreenvironment (
	id integer auto_increment primary key,
	traitscoreid integer,
	-- Environment
	environmentid integer
) ENGINE=InnoDB;
create index traitscoreid_index on traitscoreenvironment (traitscoreid);
create index environmentid_index on traitscoreenvironment (environmentid);

create table traitscoreremark (
	id integer auto_increment primary key,
	traitscoreid integer,
	-- Comment, Scores
	type varchar(50),
	remark text
) ENGINE=InnoDB;
create index traitscoreid_index on traitscoreremark (traitscoreid);

create table traitscorepathogenrace (
	id integer auto_increment primary key,
	traitscoreid integer,
	-- Pathogen_race
	isolateid integer
) ENGINE=InnoDB;
create index traitscoreid_index on traitscorepathogenrace (traitscoreid);
create index isolateid_index on traitscorepathogenrace (isolateid);