-- updated 040423 DDH

-- Evaluation: pathology->trait->traitstudy
-- Gene_class: pathology->geneclasspathology->geneclass
-- Resistance_gene: pathology->genepathology->gene (NL 28Dec2004)
-- Resistant_allele: pathology->allelepathology->allele (NL 28Dec2004)
-- Resistant_line: pathology->germplasmpathology->germplasm (NL 28Dec2004)

create table pathology (
	id integer auto_increment primary key,
	name varchar(255),
	-- Trait
	traitid integer,
	-- Symptoms
	symptoms text
) ENGINE=InnoDB;
create index name_index on pathology (name);
create index traitid_index on pathology (traitid);

create table pathologysynonym (
	id integer auto_increment primary key,
	pathologyid integer,
	-- Other_name
	type varchar(100),
	name varchar(100)
) ENGINE=InnoDB;
create index pathologyid_index on pathologysynonym (pathologyid);

create table pathologytype (
	id integer auto_increment primary key,
	pathologyid integer,
	-- Type
	type varchar(100)
) ENGINE=InnoDB;
create index pathologyid_index on pathologytype (pathologyid);

create table pathologyhostspecies (
	id integer auto_increment primary key,
	pathologyid integer,
	-- Host_species
	speciesid integer
) ENGINE=InnoDB;
create index pathologyid_index on pathologyhostspecies (pathologyid);
create index speciesid_index on pathologyhostspecies (speciesid);

create table pathologycausalorganism (
	id integer auto_increment primary key,
	pathologyid integer,
	-- Causal_organism
	speciesid integer
) ENGINE=InnoDB;
create index pathologyid_index on pathologycausalorganism (pathologyid);
create index speciesid_index on pathologycausalorganism (speciesid);

create table pathologyvector (
	id integer auto_increment primary key,
	pathologyid integer,
	-- Vector
	speciesid integer
) ENGINE=InnoDB;
create index pathologyid_index on pathologyvector (pathologyid);
create index speciesid_index on pathologyvector (speciesid);

create table pathologyreference (
	id integer auto_increment primary key,
	pathologyid integer,
	-- Reference
	referenceid integer
) ENGINE=InnoDB;
create index pathologyid_index on pathologyreference (pathologyid);
create index referenceid_index on pathologyreference (referenceid);

create table pathologyimage (
	id integer auto_increment primary key,
	pathologyid integer,
	-- Image
	imageid integer
) ENGINE=InnoDB;
create index pathologyid_index on pathologyimage (pathologyid);
create index imageid_index on pathologyimage (imageid);