-- updated 040423 DDH

-- QTL: traitstudy->qtltraitstudy->qtl
-- Linkage_data: removed

-- 23Nov2004NL: changed qtlanalysismethod datatype to text (was varchar) to avoid truncation.
-- 22Dec2004NL: added germplasmdescription, germplasmscore, parentalmeansd, phenotypicr2, traitstudygeneticr2

create table traitstudy (
	id integer auto_increment primary key,
	name varchar(255),
	-- Trait
	traitid integer,
	-- Pathology
	pathologyid integer,
	-- Mapping_data
	mapdataid integer,
	-- Population_size
	populationsize integer,
	-- Population_type
	populationtype varchar(255),
	-- Number_of_markers_tested
	markerstested integer,
	-- QTLs_found
	qtlsfound integer
) ENGINE=InnoDB;
create index name_index on traitstudy (name);
create index traitid_index on traitstudy (traitid);
create index pathologyid_index on traitstudy (pathologyid);
create index mapdataid_index on traitstudy (mapdataid);

create table traitstudyreference (
	id integer auto_increment primary key,
	traitstudyid integer,
	-- Reference
	referenceid integer
) ENGINE=InnoDB;
create index traitstudyid_index on traitstudyreference (traitstudyid);
create index referenceid_index on traitstudyreference (referenceid);

create table traitstudyremark (
	id integer auto_increment primary key,
	traitstudyid integer,
	-- Description, Protocol, Values, Statistics
	-- Type_I_error_rate_per_locus, R2_definition, Comment
	type varchar(50),
	remark text
) ENGINE=InnoDB;
create index traitstudyid_index on traitstudyremark (traitstudyid);

create table traitstudyontology (
        id integer auto_increment primary key,
	traitstudyid integer,
	dbname varchar(100),
	-- Ontology[0]
	accession varchar(100),
	url varchar(100),
	-- Ontology[1]
	remark varchar(255)
) ENGINE=InnoDB;
create index traitstudyid_index on traitstudyontology (traitstudyid);

create table traitstudyenvironment (
	id integer auto_increment primary key,
	traitstudyid integer,
	-- Environment
	environmentid integer
) ENGINE=InnoDB;
create index traitstudyid_index on traitstudyenvironment (traitstudyid);
create index environmentid_index on traitstudyenvironment (environmentid);

create table traitstudyparentaldescription (
	id integer auto_increment primary key,
	traitstudyid integer,
	-- Parental_description[0]
	germplasmid integer,
	-- Parental_description[1]
	description text
) ENGINE=InnoDB;
create index traitstudyid_index on traitstudyparentaldescription (traitstudyid);
create index germplasmid_index on traitstudyparentaldescription (germplasmid);

create table traitstudyqtlanalysismethod (
	id integer auto_increment primary key,
	traitstudyid integer,
	-- QTL_analysis_method
	qtlanalysismethod text
) ENGINE=InnoDB;
create index traitstudyid_index on traitstudyqtlanalysismethod (traitstudyid);

create table traitstudyheritability (
	id integer auto_increment primary key,
	traitstudyid integer,
	-- Heritability[0]
	heritability float,
	-- Heritability[1]
	description varchar(100)
) ENGINE=InnoDB;
create index traitstudyid_index on traitstudyheritability (traitstudyid);

create table traitstudyimage (
	id integer auto_increment primary key,
	traitstudyid integer,
	-- Image
	imageid integer
) ENGINE=InnoDB;
create index traitstudyid_index on traitstudyimage (traitstudyid);
create index imageid_index on traitstudyimage (imageid);

create table traitstudytraitscore (
	id integer auto_increment primary key,
	traitstudyid integer,
	-- Trait_scores
	traitscoreid integer
) ENGINE=InnoDB;
create index traitstudyid_index on traitstudytraitscore (traitstudyid);
create index traitscoreid_index on traitstudytraitscore (traitscoreid);

create table traitstudydatasource (
	id integer auto_increment primary key,
	traitstudyid integer,
	-- Data_source
	colleagueid integer,
	date varchar(25)
) ENGINE=InnoDB;
create index traitstudyid_index on traitstudydatasource (traitstudyid);
create index colleagueid_index on traitstudydatasource (colleagueid);

create table traitstudydatacurator (
	id integer auto_increment primary key,
	traitstudyid integer,
	-- Data_curated
	colleagueid integer,
	date varchar(25)
) ENGINE=InnoDB;
create index traitstudyid_index on traitstudydatacurator (traitstudyid);
create index colleagueid_index on traitstudydatacurator (colleagueid);

create table traitstudyparentalmeansd (
	id integer auto_increment primary key,
	traitstudyid integer,
	-- Parental_Mean_SD
	germplasmid integer,
	-- Parental_Mean_SD[1], [2]
	stddevone float,
	stddevtwo float
) ENGINE=InnoDB;
create index traitstudyid_index on traitstudyparentalmeansd (traitstudyid);
create index germplasmid_index on traitstudyparentalmeansd (germplasmid);

create table traitstudyphenotypicr2 (
	id integer auto_increment primary key,
	traitstudyid integer,
	-- Phenotypic_R2
	phenotypicr2 float,
	-- Phenotypic_R2[1]
	comments varchar(50)
) ENGINE=InnoDB;
create index traitstudyid_index on traitstudyphenotypicr2 (traitstudyid);

create table traitstudygeneticr2 (
	id integer auto_increment primary key,
	traitstudyid integer,
	-- Genetic_R2
	geneticr2 float
) ENGINE=InnoDB;
create index traitstudyid_index on traitstudygeneticr2 (traitstudyid);

create table traitstudygermplasmdescription (
	id integer auto_increment primary key,
	traitstudyid integer,
	-- Germplasm_description
	germplasmid integer,
	-- Germplasm_description[1], [2]
	characteristicone varchar(25),
	characteristictwo varchar(150)
) ENGINE=InnoDB;
create index traitstudyid_index on traitstudygermplasmdescription (traitstudyid);
create index germplasmid_index on traitstudygermplasmdescription (germplasmid);

create table traitstudygermplasmscore (
	id integer auto_increment primary key,
	traitstudyid integer,
	-- Germplasm_score
	germplasmid integer,
	-- Germplasm_score[1], [2]
	germplasmscore float,
	units varchar(25)
) ENGINE=InnoDB;
create index traitstudyid_index on traitstudygermplasmscore (traitstudyid);
create index germplasmid_index on traitstudygermplasmscore (germplasmid);