# biocode_tissue - Moorea Biocode Project
# 2006 - 2009

DROP TABLE biocode_tissue;


CREATE TABLE biocode_tissue (
  seq_num                varchar(40),    # changed from  int unsigned auto_increment not null primary key,   on 8/12/2008 by JG for JD & FMA

  bnhm_id                char(24) NOT NULL,      # PK for biocode specimens  # changed to NOT NULL 4/28/2009
  tissue_num             int unsigned,  # unique ID = bnhm_id.tissue_num

  Specimen_Num_Collector varchar(128),    ## added 5/13/2008

  HoldingInstitution     varchar(255),
  OtherCatalogNum        varchar(255),

  DateFirstEntered       date,                   # date entered or last edited
  DateLastModified       date,

  year                   mediumint unsigned,     # Darwin Core= "Year"
  month                  tinyint unsigned,       # Darwin Core= "Month"
  day                    tinyint unsigned,       # Darwin Core= "Day"

  person_subsampling     varchar(128),
  container              varchar(50),
  preservative           varchar(50),
  tissuetype             varchar(50),

  format_name96          varchar(128),           # 96 format name "Plate name"
  well_number96          varchar(10),            # 96 well number

  molecular_id           varchar(255),           # BOLD "processID" number 
	                                         #    (i.e. MBFA001-07, MBFA002-07, ....)

  notes                  text,

  from_tissue            varchar(55),             # id of tissue from which this tissue came

  tissue_barcode         varchar(255),            # for the actual barcode on the bottom 
						  #      of the little tubes 
                                                 # that the come with the matrix boxes - 
                                                 #    each tube has a little 2d unique barcode
  tissue_remaining       tinyint unsigned,         # yes/no [1/0] -- is there any tissue remaining
  batch_id		 char(24)	         # for batch upload  2008-05-16_12:22:28
  
); 


# chris: where is tissue stored? 


CREATE INDEX idz001_zzz_ix on biocode_tissue ( seq_num );
CREATE INDEX idz003_zzz_ix on biocode_tissue ( bnhm_id);
CREATE INDEX idz004_zzz_ix on biocode_tissue ( tissue_num );
CREATE INDEX idz005_zzz_ix on biocode_tissue ( entry_date );
CREATE INDEX idz007_zzz_ix on biocode_tissue ( person_subsampling );
CREATE INDEX idz008_zzz_ix on biocode_tissue ( container );
CREATE INDEX idz009_zzz_ix on biocode_tissue ( preservative );
CREATE INDEX idz010_zzz_ix on biocode_tissue ( tissuetype );
CREATE INDEX idz011_zzz_ix on biocode_tissue ( molecular_id );

CREATE INDEX idz012_zzz_ix on biocode_tissue ( year );
CREATE INDEX idz013_zzz_ix on biocode_tissue ( month );
CREATE INDEX idz014_zzz_ix on biocode_tissue ( day );
CREATE UNIQUE INDEX idz015_zzz_ix on biocode_tissue ( format_name96,well_number96 );