-- mySql version EME - Essig Museum of Entomology - Elib database
-- 2004-01-19 G.O.
-- 2005-08-12:  folded in eme_people_edit_species table

CREATE TABLE eme_people (
      
seq_num         INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,  -- Collector Number
name_full       varchar(96) NOT NULL UNIQUE, -- 
name_last       varchar(32),        --   Game
name_rest       varchar(64),        -- John C.
name_short	varchar(32),	    -- short version for labels
name_initials	varchar(15),	    # added 8/2005 for species editing
passwd          varchar(20),	    # added 8/2005 for species editing
email		varchar(96),
index_date      date,		    -- date of addition to db
entry_by        varchar(128),	    -- person who added record
notes           varchar(255),
collector	tinyint(1),
submitter	tinyint(1),
edit_species	tinyint(1),     -- # added 8/2005 allowed to edit the species table
affiliation	varchar(128),
bio		text,
num_specimens	int unsigned,
primary_name	varchar(96),       -- name_full on primary record
secondary_names varchar(255),	   -- (for primary records) other names used, comma separated
namesoup	varchar(255),      -- name_full + name_short + secondary_names
yearofbirth	mediumint unsigned,
yearofdeath	mediumint unsigned
 );

create index eme_people_namefull on eme_people (name_full);
create index eme_people_name_last on eme_people (name_last);
create index eme_people_secondary_names on eme_people (secondary_names);

grant insert on eme_people to 'nobody'@'localhost';

-- select e.CollectorNumber,count(*) from eme e, eme_people p where e.CollectorNumber=p.seq_num group by CollectorNumber;
-- update eme_people set num_specimens=3 where seq_num=99

-- insert into eme_people (seq_num,name_full,name_last,collector) values (0,"J. A.  Chemsak","Chemsak",1);