-- updated 040423 DDH
-- NL 29Nov2004: changed phenotype from varchar to text because of truncations

-- Probe: allele->allelegene->geneclone->probe
-- Mutagen: removed
-- 3Point: removed
-- Df_Dup: removed
-- Last_update: removed

-- need reference links from Compound_allele/Component_allele?

create table allele (
	id integer auto_increment primary key,
	name varchar(255)
) ENGINE=InnoDB;
create index name_index on allele (name);

create table allelereference (
	id integer auto_increment primary key,
	alleleid integer,
	-- Reference
	referenceid integer
) ENGINE=InnoDB;
create index alleleid_index on allelereference (alleleid);
create index referenceid_index on allelereference (referenceid);

create table allelesynonym (
	id integer auto_increment primary key,
	alleleid integer,
	-- Correct_name, Other_name, Previous_name, Current_name, Synonym
	type varchar(100),
	name varchar(100),
	referenceid integer
) ENGINE=InnoDB;
create index alleleid_index on allelesynonym (alleleid);
create index referenceid_index on allelesynonym (referenceid);

create table allelecomponentallele (
	id integer auto_increment primary key,
	-- Compound_allele
	alleleid integer,
	-- Component_allele
	componentallele_alleleid integer
) ENGINE=InnoDB;
create index alleleid_index on allelecomponentallele (alleleid);
create index componentallele_alleleid_index on allelecomponentallele (componentallele_alleleid);

create table allelegene (
	id integer auto_increment primary key,
	alleleid integer,
	-- Gene
	geneid integer
) ENGINE=InnoDB;
create index alleleid_index on allelegene (alleleid);
create index geneid_index on allelegene (geneid);

create table allelegeneproduct (
	id integer auto_increment primary key,
	alleleid integer,
	-- Gene_product
	geneproductid integer
) ENGINE=InnoDB;
create index alleleid_index on allelegeneproduct (alleleid);
create index geneproductid_index on allelegeneproduct (geneproductid);

create table allelegermplasm (
	id integer auto_increment primary key,
	alleleid integer,
	-- Hexaploid_stock, Tetraploid_stock, Diploid_stock
	-- Alien_species, Near_isogenic_stock,
	-- Unnamed_synthetic_stock
	-- Homologous_chromosome_substitution_line,
	-- Alien_chromosome_addition_line,
	-- Alien_chromosome_substitution_line,
	-- Translocation_line, Germplasm
	type varchar(100),
	germplasmid integer,
	referenceid integer
) ENGINE=InnoDB;
create index alleleid_index on allelegermplasm (alleleid);
create index germplasmid_index on allelegermplasm (germplasmid);
create index referenceid_index on allelegermplasm (referenceid);

create table allelepathology (
	id integer auto_increment primary key,
	alleleid integer,
	-- Pathology
	pathologyid integer
) ENGINE=InnoDB;
create index alleleid_index on allelepathology (alleleid);
create index pathologyid_index on allelepathology (pathologyid);

create table allelephenotype (
	id integer auto_increment primary key,
	alleleid integer,
	-- Phenotype
	phenotype text
) ENGINE=InnoDB;
create index alleleid_index on allelephenotype (alleleid);

create table alleleproperty (
	id integer auto_increment primary key,
	alleleid integer,
	-- Properties: see booleans in model
	property varchar(50)
) ENGINE=InnoDB;
create index alleleid_index on alleleproperty (alleleid);

create table alleleimage (
	id integer auto_increment primary key,
	alleleid integer,
	-- Image
	imageid integer
) ENGINE=InnoDB;
create index alleleid_index on alleleimage (alleleid);
create index imageid_index on alleleimage (imageid);

create table alleleremark (
	id integer auto_increment primary key,
	alleleid integer,
	-- Comment, Interactions, Origin
	type varchar(50),
	remark text
) ENGINE=InnoDB;
create index alleleid_index on alleleremark (alleleid);

create table allelewgcreference (
	id integer auto_increment primary key,
	alleleid integer,
	-- Numbered_reference
	number integer,
	referenceid integer
) ENGINE=InnoDB;
create index alleleid_index on allelewgcreference (alleleid);
create index referenceid_index on allelewgcreference (referenceid);

create table alleledatasource (
	id integer auto_increment primary key,
	alleleid integer,
	-- Data_source
	colleagueid integer,
	date varchar(25)
) ENGINE=InnoDB;
create index alleleid_index on alleledatasource (alleleid);
create index colleagueid_index on alleledatasource (colleagueid);

create table alleleinfosource (
	id integer auto_increment primary key,
	alleleid integer,
	-- Information_source
	referenceid integer
) ENGINE=InnoDB;
create index alleleid_index on alleleinfosource (alleleid);
create index referenceid_index on alleleinfosource (referenceid);

create table alleledatacurator (
	id integer auto_increment primary key,
	alleleid integer,
	-- Data_curated
	colleagueid integer,
	date varchar(25)
) ENGINE=InnoDB;
create index alleleid_index on alleledatacurator (alleleid);
create index colleagueid_index on alleledatacurator (colleagueid);