-- updated 040617 DDH
-- 29Nov2004 NL changed twopointdata.linkage from varchar(50) to varchar(150) to avoid truncation

-- Gene: twopointdata->genetwopointdata->gene
-- Min: removed
-- Max: removed
-- QTL: removed
-- Map_data: removed
-- Linkage_data: removed

create table twopointdata (
	id integer auto_increment primary key,
	name varchar(255),
	-- Locus_1
	locusone_locusid integer,
	-- Locus_2
	locustwo_locusid integer,
	-- Distance
	distance float,
	-- Error
	error float,
	-- Distance_units
	distanceunits varchar(25),
	-- Linkage
	linkage varchar(150),
	-- Trait_marker
	traitmarker tinyint(1),
	-- Chromosome
	chromosome varchar(25),
	-- Chromosome_arm
	chromosomearm varchar(25),
	-- Species
	speciesid integer,
	-- Female_Parent
	femaleparent_germplasmid integer,
	-- Male_Parent
	maleparent_germplasmid integer,
	-- Number_of_individuals
	individuals integer,
	-- Contact
	contact_colleagueid integer
) ENGINE=InnoDB;
create index name_index on twopointdata (name);
create index locusone_locusid_index on twopointdata (locusone_locusid);
create index locustwo_locusid_index on twopointdata (locustwo_locusid);
create index contact_colleagueid_index on twopointdata (contact_colleagueid);

create table twopointdataurl (
        id integer auto_increment primary key,
	twopointdataid integer,
	-- URL[0]
	url varchar(255),
	-- URL[1]
	description varchar(255)
) ENGINE=InnoDB;
create index twopointdataid_index on twopointdataurl (twopointdataid);

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

create table twopointdatareference (
	id integer auto_increment primary key,
	twopointdataid integer,
	-- Reference
	referenceid integer
) ENGINE=InnoDB;
create index twopointdataid_index on twopointdatareference (twopointdataid);
create index referenceid_index on twopointdatareference (referenceid);

create table twopointdataremark (
	id integer auto_increment primary key,
	twopointdataid integer,
	-- Population, Generation, Method, Remark
	type varchar(50),
	remark text
) ENGINE=InnoDB;
create index twopointdataid_index on twopointdataremark (twopointdataid);

create table twopointdatadatasource (
	id integer auto_increment primary key,
	twopointdataid integer,
	-- Data_source
	colleagueid integer,
	date varchar(25)
) ENGINE=InnoDB;
create index twopointdataid_index on twopointdatadatasource (twopointdataid);
create index colleagueid_index on twopointdatadatasource (colleagueid);