-- updated 040506 DDH

-- Probe: library->probe
-- Sequence: library->sequence

create table library (
	id integer auto_increment primary key,
	name varchar(255),
	-- Species
	speciesid integer,
	-- Germplasm
	germplasmid integer,
	-- Developmental_stage
	developmentalstage varchar(255),
	-- Tissue
	tissue varchar(255),
	-- Treatment
	treatment varchar(255),
	-- Chromosome
	chromosome varchar(100),
	-- Vector
	vector varchar(100),
	-- Cloning_site
	cloningsite varchar(100),
	strain varchar(100),
	antibiotic varchar(100),
	-- Sequencing_primers
	sequencingprimers varchar(100),
	-- Number_of_clones
	clonecount varchar(100),
	-- Clone_size
	clonesize varchar(100),
	-- Coverage
	coverage varchar(100),
	-- Source
	source_colleagueid integer,
	-- Reference
	referenceid integer,
	-- WWW_page
	wwwpage varchar(255),
	-- Date
	date varchar(25),
	-- Remark
	remark text
) ENGINE=InnoDB;
create index name_index on library (name);
create index speciesid_index on library (speciesid);
create index germplasmid_index on library (germplasmid);
create index developmentalstage_index on library (developmentalstage);
create index tissue_index on library (tissue);
create index treatment_index on library (treatment);
create index source_colleagueid_index on library (source_colleagueid);
create index referenceid_index on library (referenceid);

create table librarytype (
        id integer auto_increment primary key,
	libraryid integer,
	-- Type
	type varchar(100)
) ENGINE=InnoDB;
create index libraryid_index on librarytype (libraryid);

create table librarydatasource (
	id integer auto_increment primary key,
	libraryid integer,
	-- Data_source
	colleagueid integer,
	date varchar(25)
) ENGINE=InnoDB;
create index libraryid_index on librarydatasource (libraryid);
create index colleagueid_index on librarydatasource (colleagueid);