-- 9apr04 DEM
-- updated 040423 DDH
-- 29Nov2004 NL changed polymorphism.bandsize from varchar to text to avoid truncation
 
-- TABLE: removed

create table polymorphism (
	id integer auto_increment primary key,
	name varchar(100),
	-- Probe
	probeid integer,
	-- Enzyme
	restrictionenzymeid integer,
	-- Band_size.  A column header for polymorphismpattern data.
	bandsize text
) ENGINE=InnoDB;
create index name_index on polymorphism (name);
create index probeid_index on polymorphism (probeid);
create index restrictionenzymeid_index on polymorphism (restrictionenzymeid);

--      Size ?Gel Float ?Text ?Germplasm // Gel,Size,Intensity,Germplasm
--              // Each subfield can be multivalued. 
--              // Example, Polymorphism "BCD1088 EcoRI"
create table polymorphismsize (
        id integer auto_increment primary key,
        polymorphismid integer,
        -- Size, Size[1], Size[2], Size[3]
        gelid integer,
        size float,
        intensity varchar(20),
        germplasmid integer
) ENGINE=InnoDB;
create index polymorphismid_index on polymorphismsize (polymorphismid);
create index gelid_index on polymorphismsize (gelid);
create index germplasmid_index on polymorphismsize (germplasmid);

--      Value ?Species Text ?Germplasm  // Species, Allele, Germplasm
--              // Each subfield can be multivalued. 
--              // Example, Polymorphism "PSR100 5B DraI"
create table polymorphismvalue (
        id integer auto_increment primary key,
        polymorphismid integer,
        -- Value, Value[1], Value[2]
	speciesid integer,
	allele varchar(5),
	germplasmid integer
) ENGINE=InnoDB;
create index polymorphismid_index on polymorphismvalue (polymorphismid);
create index speciesid_index on polymorphismvalue (speciesid);
create index germplasmid_index on polymorphismvalue (germplasmid);

--      Pattern Text ?Germplasm // Text is a string of 1's, 0's, ?'s etc.
--		// Each subfield can be multivalued. 
--		// Example, Polymorphism "BCD1700 EcoRI"
create table polymorphismpattern (
        id integer auto_increment primary key,
        polymorphismid integer,
        -- Pattern, Pattern[1]
	pattern varchar(255),
	germplasmid integer
) ENGINE=InnoDB;
create index polymorphismid_index on polymorphismpattern (polymorphismid);
create index germplasmid_index on polymorphismpattern (germplasmid);

create table polymorphismgermplasm (
        id integer auto_increment primary key,
        polymorphismid integer,
        -- Germplasm
	germplasmid integer
) ENGINE=InnoDB;
create index polymorphismid_index on polymorphismgermplasm (polymorphismid);
create index germplasmid_index on polymorphismgermplasm (germplasmid);

create table polymorphismimage (
        id integer auto_increment primary key,
        polymorphismid integer,
        -- Image
	imageid integer
) ENGINE=InnoDB;
create index polymorphismid_index on polymorphismimage (polymorphismid);
create index imageid_index on polymorphismimage (imageid);

create table polymorphismremark (
        id integer auto_increment primary key,
        polymorphismid integer,
        -- Remarks
	remark text
) ENGINE=InnoDB;
create index polymorphismid_index on polymorphismremark (polymorphismid);

create table polymorphismreference (
        id integer auto_increment primary key,
        polymorphismid integer,
        -- Reference
	referenceid integer
) ENGINE=InnoDB;
create index polymorphismid_index on polymorphismreference (polymorphismid);
create index referenceid_index on polymorphismreference (referenceid);

create table polymorphismdatasource (
        id integer auto_increment primary key,
        polymorphismid integer,
        -- Data_source, Data_source[1]
	colleagueid integer,
	date varchar(25)
) ENGINE=InnoDB;
create index polymorphismid_index on polymorphismdatasource (polymorphismid);
create index colleagueid_index on polymorphismdatasource (colleagueid);