CREATE TABLE `sample` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `stock_sample_id` int(10) unsigned DEFAULT NULL,
  `cmnt_id` int(10) unsigned DEFAULT NULL,
  `sample_kind_id` int(10) unsigned NOT NULL DEFAULT '0',
  `name` char(50) NOT NULL DEFAULT '',
  `container_id` int(10) unsigned NOT NULL DEFAULT '0',
  `container_coord` char(8) DEFAULT NULL,
  `is_finished` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `is_dropped` tinyint(4) NOT NULL DEFAULT '0',
  `volume` float DEFAULT NULL,
  `concentration` float DEFAULT NULL,
  `state_called_by` int(10) unsigned DEFAULT NULL,
  `creation_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `concentration_unit` enum('unit/ul','X','mg/ml','ug/ml','ng/ul','pg/ul','mM','uM','nM','pM') DEFAULT NULL,
  `sample_description_id` int(10) unsigned NOT NULL,
  `alias` char(255) DEFAULT NULL,
  `is_pool` tinyint(3) DEFAULT NULL,
  `sample_progression_id` int(10) unsigned DEFAULT NULL,
  `sample_qc_status_id` int(10) unsigned DEFAULT NULL,
  `sample_group_id` int(10) unsigned DEFAULT NULL,
  `state` enum('in_progress','failed','on_hold','active','inactive') NOT NULL DEFAULT 'active',
  PRIMARY KEY (`id`),
  UNIQUE KEY `container` (`container_id`,`container_coord`),
  KEY `name` (`name`),
  KEY `is_finished` (`is_finished`),
  KEY `sample_kind_id` (`sample_kind_id`),
  KEY `sample_FKIndex1` (`container_id`),
  KEY `sample_FKIndex3` (`stock_sample_id`),
  KEY `sample_creation_date` (`creation_date`),
  KEY `sample_FKIndex4` (`cmnt_id`),
  KEY `sample_FKIndex5` (`state_called_by`),
  KEY `sample_FKIndex9` (`sample_description_id`),
  KEY `sample_group_id_fk` (`sample_group_id`),
  KEY `sample_ibfk_8_idx` (`sample_progression_id`),
  KEY `sample_ibfk_9_idx` (`sample_qc_status_id`),
  KEY `sample_state_idx` (`state`),
  CONSTRAINT `FKC9C775AAFA192AE5` FOREIGN KEY (`container_id`) REFERENCES `container` (`id`),
  CONSTRAINT `sample_group_id_fk` FOREIGN KEY (`sample_group_id`) REFERENCES `sample_group` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `sample_ibfk_1` FOREIGN KEY (`container_id`) REFERENCES `container` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `sample_ibfk_2` FOREIGN KEY (`sample_kind_id`) REFERENCES `sample_kind` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `sample_ibfk_3` FOREIGN KEY (`stock_sample_id`) REFERENCES `sample` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `sample_ibfk_4` FOREIGN KEY (`cmnt_id`) REFERENCES `cmnt` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `sample_ibfk_7` FOREIGN KEY (`sample_description_id`) REFERENCES `sample_description` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `sample_ibfk_8` FOREIGN KEY (`sample_progression_id`) REFERENCES `sample_progression` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `sample_ibfk_9` FOREIGN KEY (`sample_qc_status_id`) REFERENCES `sample_qc_status` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=5438413 DEFAULT CHARSET=utf8;

CREATE TABLE `sample_attribute` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sample_id` int(10) unsigned NOT NULL,
  `attribute` varchar(255) NOT NULL,
  `value` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sample_attribute_unique` (`attribute`,`sample_id`),
  KEY `sample_attribute_FKIndex1` (`sample_id`),
  CONSTRAINT `sample_attribute_ibfk_1` FOREIGN KEY (`sample_id`) REFERENCES `sample` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=13954931 DEFAULT CHARSET=utf8;

CREATE TABLE `sample_kind` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` char(50) NOT NULL DEFAULT '',
  `label` char(5) DEFAULT NULL,
  `concentration_unit` char(10) DEFAULT NULL,
  `desc_table` char(20) DEFAULT NULL,
  `category` enum('physiological','reagent','other') NOT NULL DEFAULT 'other',
  `user_accessible` tinyint(1) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `sample_kind_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=80 DEFAULT CHARSET=utf8;