-- updated 040423 DDH

-- Mapping_data: species->mapdataspecies->mapdata
-- Disease: species->pathologyhostspecies->pathology
-- Causes: species->pathologycausalorganism->pathology
-- Vector_of: species->pathologyvector->pathology
-- DNA_library: species->library
-- Sequence: removed
-- Linkage_data: removed

create table species (
	id integer auto_increment primary key,
	name varchar(255),
	-- Genus
	genus varchar(100),
	-- Species_epithet
	species varchar(100),
	-- Authority
	authority varchar(255),
	-- Genome
	genome varchar(25)
) ENGINE=InnoDB;
create index name_index on species (name);

create table speciessynonym (
	id integer auto_increment primary key,
	speciesid integer,
	-- Correct_name, Full_name, Synonym, Common_name
	type varchar(100),
	name varchar(100)
) ENGINE=InnoDB;
create index speciesid_index on speciessynonym (speciesid);

create table speciesvariety (
	id integer auto_increment primary key,
	speciesid integer,
	-- Variety
	variety varchar(255)
) ENGINE=InnoDB;
create index speciesid_index on speciesvariety (speciesid);

create table speciesreference (
	id integer auto_increment primary key,
	speciesid integer,
	-- Reference
	referenceid integer
) ENGINE=InnoDB;
create index speciesid_index on speciesreference (speciesid);
create index referenceid_index on speciesreference (referenceid);

create table speciesimage (
	id integer auto_increment primary key,
	speciesid integer,
	-- Image
	imageid integer
) ENGINE=InnoDB;
create index speciesid_index on speciesimage (speciesid);
create index imageid_index on speciesimage (imageid);

create table speciesremark (
        id integer auto_increment primary key,
	speciesid integer,
	-- Haploid_chromosome_number
	type varchar(50),
	remark text
) ENGINE=InnoDB;
create index speciesid_index on speciesremark (speciesid);