-- updated 040502 DDH

-- Entries: removed
-- Please_see: removed

create table collection (
	id integer auto_increment primary key,
	name varchar(255),
        -- Mail
        mail varchar(255),
        -- Country
        country varchar(100),
        -- Phone
        phone varchar(100),
        -- Fax
        fax varchar(100),
	-- E_mail
        email varchar(100),
        -- Cable
        cable varchar(100),
        -- Telex
        telex varchar(100),
	-- Curator
	curator_colleagueid integer,
	-- Contained_in, Contains
	containedin_collectionid integer,
	-- IPGRI_code
	ipgricode varchar(100),
	-- Please_see
	pleasesee_collectionid integer
) ENGINE=InnoDB;
create index name_index on collection (name);
create index curator_colleagueid_index on collection (curator_colleagueid);
create index containedin_collectionid_index on collection (containedin_collectionid);

create table collectionremark (
        id integer auto_increment primary key,
	collectionid integer,
	-- Description, WWW_page
	type varchar(50),
	remark text
) ENGINE=InnoDB;
create index collectionid_index on collectionremark (collectionid);

create table collectionspecies (
	id integer auto_increment primary key,
	collectionid integer,
	-- Species[0]
	speciesid integer,
	-- Species[1]
	entries integer
) ENGINE=InnoDB;
create index collectionid_index on collectionspecies (collectionid);
create index speciesid_index on collectionspecies (speciesid);

create table collectionupdate (
	id integer auto_increment primary key,
	collectionid integer,
	-- Update[0]
	sourceid integer,
	-- Update[1]
	date varchar(25)
) ENGINE=InnoDB;
create index collectionid_index on collectionupdate (collectionid);
create index sourceid_index on collectionupdate (sourceid);

create table collectiondatasource (
	id integer auto_increment primary key,
	collectionid integer,
	-- Data_source[0]
	sourceid integer,
	-- Data_source[1]
	date varchar(25)
) ENGINE=InnoDB;
create index collectionid_index on collectiondatasource (collectionid);
create index sourceid_index on collectiondatasource (sourceid);