  * An animal sample is a type of sample
  * The discriminative value is on the sample type
  */
-drop table live_sample;
+
 CREATE TABLE live_sample (
 
     id                        INT(10) UNSIGNED NOT NULL,

-- this table holds all the external/ASTD database names 
CREATE TABLE external_db (

    id                          INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    name                        VARCHAR(100) NOT NULL,
    short_name                  VARCHAR(40) NOT NULL,
    version                     VARCHAR(15) NOT NULL DEFAULT '',

	PRIMARY KEY(id),
	KEY biological_sample_idx(biological_sample_id),
	KEY parameter_idx(parameter_id),
	KEY parameter_stable_idx(parameter_stable_id),
	KEY population_idx(population_id)

) COLLATE=utf8_general_ci ENGINE=MyISAM;


/**
 * observation_population
 * An observation is a experimental parameter measurement (data point, image 
 * record, etc.) 
 * of a phenotype of a given control/experimental biological sample.
 * id: primary surrogate key for this table
 * population_id: grouping index for collection observations together
 * observation_id: the observation belonging to this population
 */
CREATE TABLE observation_population (

    id                         INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	observation_id             INT(10) UNSIGNED NOT NULL,
	population_id              INT(10) UNSIGNED NOT NULL,

	PRIMARY KEY(id),
	KEY population_observation_idx(population_id, observation_id)

) COLLATE=utf8_general_ci ENGINE=MyISAM;

CREATE TABLE population (

    id                         INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    parameter_id               INT(10) UNSIGNED NOT NULL,
    organisation_id            INT(10) UNSIGNED NOT NULL,
    acc                        VARCHAR(20) NOT NULL,
    db_id                      INT(10) NOT NULL,
    zygosity                   ENUM('homozygote', 'heterozygote', 'hemizygote'),
    sex                        ENUM('female', 'hermaphrodite', 'male'),
    control_batches            INT(10) UNSIGNED NOT NULL DEFAULT 0,
    experimental_batches       INT(10) UNSIGNED NOT NULL DEFAULT 0,
    concurrent_controls        TINYINT(1) DEFAULT 0,

    PRIMARY KEY(id),
    KEY parameter_idx(parameter_id),
    KEY genomic_feature_idx (acc, db_id)

) COLLATE=utf8_general_ci ENGINE=MyISAM;


/**
 * text_observation
