-- updated 040423 DDH
-- rev. 19Aug2004 NL: added Candidate_locus
-- rev. 29Dec2004 NL: added Reference_allele_and_background, Inheritance,
--	Description, First_mutation, tho no current objects, per DEM.


-- Allele[0]: gene->allelegene->allele
-- QTL: gene->qtlassociatedgene->qtl
-- Allele[1]: removed
-- Reference_allele: removed
-- BGS_photo: removed (27Sep2004 NL added genebgsphoto)
-- 3Point: removed
-- Df_Dup: removed
-- Related_genes: removed
-- Gene_product[1]: removed
-- need reference links from Compound_gene/Component_gene? -- no, per DEM 28Dec2004

create table gene (
	id integer auto_increment primary key,
	name varchar(255),
	-- Full_name
	fullname varchar(255),
	-- Orthologous_gene_set
	orthologousgeneset_genesetid integer,
	-- Compound_gene
	compoundgene_geneid integer
) ENGINE=InnoDB;
create index name_index on gene (name);
create index orthologousgeneset_genesetid_index on gene (orthologousgeneset_genesetid);
create index compoundgene_geneid_index on gene (compoundgene_geneid);

-- This table isn't needed.  See table locusassociatedgene.
-- create table genelocus (
-- 	id integer auto_increment primary key,
-- 	geneid integer,
-- 	-- Locus
-- 	locusid integer,
-- 	howmapped varchar(100)
-- ) ENGINE=InnoDB;
-- create index geneid_index on genelocus (geneid);
-- create index locusid_index on genelocus (locusid);

create table genereference (
	id integer auto_increment primary key,
	geneid integer,
	-- Reference
	referenceid integer
) ENGINE=InnoDB;
create index geneid_index on genereference (geneid);
create index referenceid_index on genereference (referenceid);

create table genegeneclass (
	id integer auto_increment primary key,
	geneid integer,
	-- Gene_class
	geneclassid integer
) ENGINE=InnoDB;
create index geneid_index on genegeneclass (geneid);
create index geneclassid_index on genegeneclass (geneclassid);

create table genepathology (
	id integer auto_increment primary key,
	geneid integer,
	-- Pathology
	pathologyid integer
) ENGINE=InnoDB;
create index geneid_index on genepathology (geneid);
create index pathologyid_index on genepathology (pathologyid);

create table geneurl (
	id integer auto_increment primary key,
	geneid integer,
	-- URL
	url varchar(255),
	description varchar(255)
) ENGINE=InnoDB;
create index geneid_index on geneurl (geneid);

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

create table genecomponentgene (
	id integer auto_increment primary key,
	geneid integer,
	-- Component_gene
	componentgene_geneid integer
) ENGINE=InnoDB;
create index geneid_index on genecomponentgene (geneid);
create index componentgene_geneid_index on genecomponentgene (componentgene_geneid);

create table genegeneproduct (
	id integer auto_increment primary key,
	geneid integer,
	-- Gene_product[0]
	geneproductid integer
) ENGINE=InnoDB;
create index geneid_index on genegeneproduct (geneid);
create index geneproductid_index on genegeneproduct (geneproductid);

create table genechromosome (
	id integer auto_increment primary key,
	geneid integer,
	-- Chromosome
	chromosome varchar(25),
	referenceid integer
) ENGINE=InnoDB;
create index geneid_index on genechromosome (geneid);
create index referenceid_index on genechromosome (referenceid);

create table genechromosomearm (
	id integer auto_increment primary key,
	geneid integer,
	-- Chromosome_arm
	chromosomearm varchar(25),
	referenceid integer
) ENGINE=InnoDB;
create index geneid_index on genechromosomearm (geneid);
create index referenceid_index on genechromosomearm (referenceid);

create table genegermplasm (
	id integer auto_increment primary key,
	geneid 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
	type varchar(100),
	germplasmid integer,
	referenceid integer
) ENGINE=InnoDB;
create index geneid_index on genegermplasm (geneid);
create index germplasmid_index on genegermplasm (germplasmid);
create index referenceid_index on genegermplasm (referenceid);

create table generemark (
	id integer auto_increment primary key,
	geneid integer,
	-- Comment, Origin
	type varchar(50),
	remark text
) ENGINE=InnoDB;
create index geneid_index on generemark (geneid);

create table geneclone (
	id integer auto_increment primary key,
	geneid integer,
	-- Clone
	probeid integer
) ENGINE=InnoDB;
create index geneid_index on geneclone (geneid);
create index probeid_index on geneclone (probeid);

create table genesequence (
	id integer auto_increment primary key,
	geneid integer,
	-- Sequence
	sequenceid integer
) ENGINE=InnoDB;
create index geneid_index on genesequence (geneid);
create index sequenceid_index on genesequence (sequenceid);

create table geneimage (
	id integer auto_increment primary key,
	geneid integer,
	-- Image
	imageid integer
) ENGINE=InnoDB;
create index geneid_index on geneimage (geneid);
create index imageid_index on geneimage (imageid);

create table genetwopointdata (
	id integer auto_increment primary key,
	geneid integer,
	-- 2_point_data
	twopointdataid integer
) ENGINE=InnoDB;
create index geneid_index on genetwopointdata (geneid);
create index twopointdataid_index on genetwopointdata (twopointdataid);

create table genewgcreference (
	id integer auto_increment primary key,
	geneid integer,
	-- Numbered_reference
	number integer,
	referenceid integer
) ENGINE=InnoDB;
create index geneid_index on genewgcreference (geneid);
create index referenceid_index on genewgcreference (referenceid);

create table genedatasource (
	id integer auto_increment primary key,
	geneid integer,
	-- Data_source
	colleagueid integer,
	date varchar(25)
) ENGINE=InnoDB;
create index geneid_index on genedatasource (geneid);
create index colleagueid_index on genedatasource (colleagueid);

create table geneinfosource (
	id integer auto_increment primary key,
	geneid integer,
	-- Information_source
	referenceid integer
) ENGINE=InnoDB;
create index geneid_index on geneinfosource (geneid);
create index referenceid_index on geneinfosource (referenceid);

create table genedatacurator (
	id integer auto_increment primary key,
	geneid integer,
	-- Data_curated
	colleagueid integer,
	date varchar(25)
) ENGINE=InnoDB;
create index geneid_index on genedatacurator (geneid);
create index colleagueid_index on genedatacurator (colleagueid);

create table genecandidatelocus (
        id integer auto_increment primary key,
        geneid integer,
        -- Candidate_locus
        candidatelocus_locusid integer
) ENGINE=InnoDB;
create index geneid_index on genecandidatelocus (geneid);
create index candidatelocus_locusid_index on genecandidatelocus (candidatelocus_locusid);

create table genebgsphoto (
        id integer auto_increment primary key,
        geneid integer,
        -- BGS_photo
        name varchar(100)
) ENGINE=InnoDB;
create index geneid_index on genebgsphoto (geneid);

create table genereferencealleleandbackground (
        id integer auto_increment primary key,
        geneid integer,
        -- Reference_allele_and_background[0],[1]
        alleleid integer,
        germplasmid integer
) ENGINE=InnoDB;
create index geneid_index on genereferencealleleandbackground (geneid);
create index alleleid_index on genereferencealleleandbackground (alleleid);
create index germplasmid_index on genereferencealleleandbackground (germplasmid);

create table geneinheritance (
        id integer auto_increment primary key,
        geneid integer,
        -- Inheritance
        inheritance text
) ENGINE=InnoDB;
create index geneid_index on geneinheritance (geneid);

create table genedescription (
        id integer auto_increment primary key,
        geneid integer,
        -- Description
        description text
) ENGINE=InnoDB;
create index geneid_index on genedescription (geneid);

create table genefirstmutation (
        id integer auto_increment primary key,
        geneid integer,
        -- First_mutation
        firstmutation text
) ENGINE=InnoDB;
create index geneid_index on genefirstmutation (geneid);