-- updated 040423 DDH
-- rev. 23Dec2004 NL:  added polymorphism
-- rev. 28Dec2004 NL:  added trait_description, trait_score, trait_scores

-- Allele: germplasm->allelegermplasm->allele
-- Gene: germplasm->allelegermplasm->allelegene->gene (NL/16Aug2004: use genegermplasm)
-- Gene_product: germplasm->allelegermplasm->allelegeneproduct->geneproduct (NL/16Aug2004: use geneproductgermplasm)
-- Deletion_distal_to: removed
-- Full_name: removed
-- Female_Parent: removed
-- Male_Parent: removed
-- Sequence: removed
-- Coefficient_of_parentage: removed

create table germplasm (
	id integer auto_increment primary key,
	name varchar(255),
	-- Cross_number
	crossnumber varchar(100),
	-- Chromosome_configuration
	chromosomeconfiguration varchar(100),
	-- Chromosome_number
	chromosomenumber varchar(100),
	-- Selection_history
	selectionhistory_germplasmid integer,
	-- Derived_from
	derivedfrom_germplasmid integer,
	-- Chromosome_donor
	chromosomedonor varchar(100),
	-- Cytoplasm
	cytoplasm varchar(100),
	-- Primary_collection
	primarycollection_collectionid integer,
	-- Registration_No
	registrationnumber varchar(100)
) ENGINE=InnoDB;
create index name_index on germplasm (name);
create index selectionhistory_germplasmid_index on germplasm (selectionhistory_germplasmid);
create index derivedfrom_germplasmid_index on germplasm (derivedfrom_germplasmid);
create index primarycollection_collectionid_index on germplasm (primarycollection_collectionid);

create table germplasmtype (
	id integer auto_increment primary key,
	germplasmid integer,
	-- Type, Germplasm_type
	type varchar(100)
) ENGINE=InnoDB;
create index germplasmid_index on germplasmtype (germplasmid);
			
create table germplasmsynonym (
	id integer auto_increment primary key,
	germplasmid integer,
	-- Other_name, Abbreviation
	type varchar(100),
	name varchar(100)
) ENGINE=InnoDB;
create index germplasmid_index on germplasmsynonym (germplasmid);

create table germplasmspecies (
	id integer auto_increment primary key,
	germplasmid integer,
	-- Species, Subspecies, Donor_species
	type varchar(100),
	speciesid integer
) ENGINE=InnoDB;
create index germplasmid_index on germplasmspecies (germplasmid);
create index speciesid_index on germplasmspecies (speciesid);

create table germplasmcollection (
	id integer auto_increment primary key,
	germplasmid integer,
	-- Collection_and_ID
	collectionid integer,
	collectiongermplasm_germplasmid integer
) ENGINE=InnoDB;
create index germplasmid_index on germplasmcollection (germplasmid);
create index collectionid_index on germplasmcollection (collectionid);
create index collectiongermplasm_germplasmid_index on germplasmcollection (collectiongermplasm_germplasmid);

create table germplasmbreakpoint (
	id integer auto_increment primary key,
	germplasmid integer,
	-- Contains_breakpoint
	breakpointid integer
) ENGINE=InnoDB;
create index germplasmid_index on germplasmbreakpoint (germplasmid);
create index breakpointid_index on germplasmbreakpoint (breakpointid);

create table germplasmremark (
	id integer auto_increment primary key,
	germplasmid integer,
	-- Pairing_configuration, Pedigree, Market_Class, Characteristic
	-- Developed_by, Development_site, Collection_site, Date_collected
	-- Remark, Date_of_release
	type varchar(50),
	remark text
) ENGINE=InnoDB;
create index germplasmid_index on germplasmremark (germplasmid);

create table germplasmpathology (
	id integer auto_increment primary key,
	germplasmid integer,
	-- Pathology
	pathologyid integer
) ENGINE=InnoDB;
create index germplasmid_index on germplasmpathology (germplasmid);
create index pathologyid_index on germplasmpathology (pathologyid);

create table germplasmrearrangement (
	id integer auto_increment primary key,
	germplasmid integer,
	-- Rearrangement
	rearrangementid integer
) ENGINE=InnoDB;
create index germplasmid_index on germplasmrearrangement (germplasmid);
create index rearrangementid_index on germplasmrearrangement (rearrangementid);

create table germplasmreference (
	id integer auto_increment primary key,
	germplasmid integer,
	-- Reference
	referenceid integer
) ENGINE=InnoDB;
create index germplasmid_index on germplasmreference (germplasmid);
create index referenceid_index on germplasmreference (referenceid);

create table germplasmdatasource (
	id integer auto_increment primary key,
	germplasmid integer,
	-- Data_source
	colleagueid integer,
	date varchar(25)
) ENGINE=InnoDB;
create index germplasmid_index on germplasmdatasource (germplasmid);
create index colleagueid_index on germplasmdatasource (colleagueid);

create table germplasmdatacurator (
	id integer auto_increment primary key,
	germplasmid integer,
	-- Data_curated
	colleagueid integer,
	date varchar(25)
) ENGINE=InnoDB;
create index germplasmid_index on germplasmdatacurator (germplasmid);
create index colleagueid_index on germplasmdatacurator (colleagueid);

create table germplasmmapdata (
	id integer auto_increment primary key,
	germplasmid integer,
	-- Mapping_data, Linkage_data
	mapdataid integer
) ENGINE=InnoDB;
create index germplasmid_index on germplasmmapdata (germplasmid);
create index mapdataid_index on germplasmmapdata (mapdataid);

create table germplasmtwopointdata (
	id integer auto_increment primary key,
	germplasmid integer,
	-- 2_point_data
	twopointdataid integer
) ENGINE=InnoDB;
create index germplasmid_index on germplasmtwopointdata (germplasmid);
create index twopointdataid_index on germplasmtwopointdata (twopointdataid);

create table germplasmlibrary (
	id integer auto_increment primary key,
	germplasmid integer,
	-- DNA_library
	libraryid integer
) ENGINE=InnoDB;
create index germplasmid_index on germplasmlibrary (germplasmid);
create index libraryid_index on germplasmlibrary (libraryid);

create table germplasmtraitstudy (
	id integer auto_increment primary key,
	germplasmid integer,
	-- Trait_study
	traitstudyid integer
) ENGINE=InnoDB;
create index germplasmid_index on germplasmtraitstudy (germplasmid);
create index traitstudyid_index on germplasmtraitstudy (traitstudyid);

create table germplasmimage (
	id integer auto_increment primary key,
	germplasmid integer,
	-- Image
	imageid integer
) ENGINE=InnoDB;
create index germplasmid_index on germplasmimage (germplasmid);
create index imageid_index on germplasmimage (imageid);

create table germplasmpolymorphism (
	id integer auto_increment primary key,
	germplasmid integer,
	-- Polymorphism
	polymorphismid integer,
	presence varchar(10),
	bandsize varchar(100)
) ENGINE=InnoDB;
create index germplasmid_index on germplasmpolymorphism (germplasmid);
create index polymorphismid_index on germplasmpolymorphism (polymorphismid);

create table germplasmtraitdescription (
	id integer auto_increment primary key,
	germplasmid integer,
	-- Trait_description
	traitstudyid integer,
	score varchar(25),
	interpretation varchar(75)
) ENGINE=InnoDB;
create index germplasmid_index on germplasmtraitdescription (germplasmid);
create index traitstudyid_index on germplasmtraitdescription (traitstudyid);

create table germplasmtraitscore (
	id integer auto_increment primary key,
	germplasmid integer,
	-- Trait_score
	traitstudyid integer,
	score float,
	units varchar(25)
) ENGINE=InnoDB;
create index germplasmid_index on germplasmtraitscore (germplasmid);
create index traitstudyid_index on germplasmtraitscore (traitstudyid);

create table germplasmtraitscores (
	id integer auto_increment primary key,
	germplasmid integer,
	-- Trait_scores
	traitscoreid integer
) ENGINE=InnoDB;
create index germplasmid_index on germplasmtraitscores (germplasmid);
create index traitscoreid_index on germplasmtraitscores (traitscoreid);

-- For QTL Parent
create table germplasmqtl (
	id integer auto_increment primary key,
	germplasmid integer,
	-- QTL
	qtlid integer
) ENGINE=InnoDB;
create index germplasmid_index on germplasmqtl (germplasmid);
create index qtlid_index on germplasmqtl (qtlid);