-- updated 040423 DDH

-- Gene_class: reference->geneclassreference->geneclass
-- Gene_set: reference->genesetreference->geneset
-- Gene: reference->genereference->gene
-- Allele: reference->allelereference->allele
-- Locus: reference->locusreference->locus
-- Rearrangement: reference->rearrangementreference->rearrangement
-- Breakpoint: reference->breakpointreference->breakpoint
-- Probe: reference->probereference->probe
-- Sequence: reference->sequencereference->sequence
-- Germplasm: reference->germplasmreference->germplasm
-- Mapping_data: reference->mapdatareference->mapdata
-- 2_point_data: reference->twopointdatareference->twopointdata
-- Trait_study: reference->traitstudyreference->traitstudy
-- Isolate: reference->isolatereference->isolate
-- Linkage_data: removed
-- Original_title: removed
-- Institution: removed

create table reference (
	id integer auto_increment primary key,
	name varchar(255),
	-- Title
	title text,
	-- Journal
	journalid integer,
	-- Publisher
	publisher varchar(255),
	-- Year
	year year,
	-- Volume
	volume varchar(10),
	-- Page
	pages varchar(25),
	-- Abstract
	abstract text,
	-- Cited_in
	citedin_referenceid integer,
	-- Summarized_in, Summary_of
	summarizedin_referenceid integer,
	-- Contained_in, Contains
	containedin_referenceid integer
) ENGINE=InnoDB;
create index name_index on reference (name);
create index journalid_index on reference (journalid);
create index citedin_referenceid_index on reference (citedin_referenceid);
create index summarizedin_referenceid_index on reference (summarizedin_referenceid);
create index containedin_referenceid_index on reference (containedin_referenceid);

create table referenceremark (
        id integer auto_increment primary key,
	referenceid integer,
	-- Remark, Series, Type, Language, Agricola_code ("Keyword" moved to referencekeyword 2Aug2004)
	-- Gene_Catalogue_number
	type varchar(50),
	remark text
) ENGINE=InnoDB;
create index referenceid_index on referenceremark (referenceid);

create table referenceauthor (
	id integer auto_increment primary key,
	referenceid integer,
	-- Author
	authorid integer
) ENGINE=InnoDB;
create index referenceid_index on referenceauthor (referenceid);
create index authorid_index on referenceauthor (authorid);

create table referenceeditor (
	id integer auto_increment primary key,
	referenceid integer,
	-- Editor
	authorid integer
) ENGINE=InnoDB;
create index referenceid_index on referenceeditor (referenceid);
create index authorid_index on referenceeditor (authorid);

create table referenceimage (
	id integer auto_increment primary key,
	referenceid integer,
	-- Image
	imageid integer
) ENGINE=InnoDB;
create index referenceid_index on referenceimage (referenceid);
create index imageid_index on referenceimage (imageid);

create table referenceexternaldb (
        id integer auto_increment primary key,
	referenceid integer,
	-- Online
	name varchar(25),
	accession varchar(25),
	-- URL
	url varchar(255)
) ENGINE=InnoDB;
create index referenceid_index on referenceexternaldb (referenceid);

create table referencekeyword (
        id integer auto_increment primary key,
        referenceid integer,
        keywordid integer
) ENGINE=InnoDB;
create index referenceid_index on referencekeyword (referenceid);
create index keywordid_index on referencekeyword (keywordid);