MySQL DB template diagram

[eboileau]

Aims/objectives.

See Data model in Sprint board.

A clear and concise description of todo items.

This is the current MySQL DB schema (version 1):

+--------------------+
| Tables_in_scimodom |
+--------------------+
| assembly           |
| association        |
| data               |
| dataset            |
| method             |
| modification       |
| modomics           |
| ncbi_taxa          |
| organism           |
| project            |
| selection          |
| taxonomy           |
| technology         |
+--------------------+

CREATE TABLE `assembly` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL,
  `taxa_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `taxa_id` (`taxa_id`),
  CONSTRAINT `assembly_ibfk_1` FOREIGN KEY (`taxa_id`) REFERENCES `ncbi_taxa` (`id`)
)

CREATE TABLE `association` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dataset_id` int(11) NOT NULL,
  `selection_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `dataset_id` (`dataset_id`,`selection_id`),
  KEY `selection_id` (`selection_id`),
  CONSTRAINT `association_ibfk_1` FOREIGN KEY (`dataset_id`) REFERENCES `dataset` (`id`),
  CONSTRAINT `association_ibfk_2` FOREIGN KEY (`selection_id`) REFERENCES `selection` (`id`)
)

CREATE TABLE `data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dataset_id` int(11) NOT NULL,
  `chrom` varchar(128) NOT NULL,
  `start` int(11) NOT NULL,
  `end` int(11) NOT NULL,
  `name` varchar(32) NOT NULL,
  `score` int(11) NOT NULL,
  `strand` varchar(1) NOT NULL,
  `thick_start` int(11) NOT NULL,
  `thick_end` int(11) NOT NULL,
  `item_rgb` varchar(128) NOT NULL,
  `coverage` int(11) NOT NULL,
  `frequency` int(11) NOT NULL,
  `ref_base` varchar(1) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `dataset_id` (`dataset_id`),
  CONSTRAINT `data_ibfk_1` FOREIGN KEY (`dataset_id`) REFERENCES `dataset` (`id`)
)

CREATE TABLE `dataset` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `project_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `file_format` varchar(32) NOT NULL,
  `modification_type` varchar(32) NOT NULL,
  `taxa_id` int(11) NOT NULL,
  `assembly_id` int(11) NOT NULL,
  `lifted` tinyint(1) NOT NULL,
  `annotation_source` varchar(128) NOT NULL,
  `annotation_version` varchar(128) NOT NULL,
  `sequencing_platform` varchar(255) DEFAULT NULL,
  `basecalling` text DEFAULT NULL,
  `bioinformatics_workflow` text DEFAULT NULL,
  `experiment` text DEFAULT NULL,
  `external_source` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `project_id` (`project_id`),
  KEY `taxa_id` (`taxa_id`),
  KEY `assembly_id` (`assembly_id`),
  CONSTRAINT `dataset_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `project` (`id`),
  CONSTRAINT `dataset_ibfk_2` FOREIGN KEY (`taxa_id`) REFERENCES `ncbi_taxa` (`id`),
  CONSTRAINT `dataset_ibfk_3` FOREIGN KEY (`assembly_id`) REFERENCES `assembly` (`id`)
)

CREATE TABLE `method` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cls` varchar(32) NOT NULL,
  `meth` varchar(128) NOT NULL,
  PRIMARY KEY (`id`)
)

CREATE TABLE `modification` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `rna` varchar(32) NOT NULL,
  `modomics_id` varchar(128) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `modomics_id` (`modomics_id`),
  CONSTRAINT `modification_ibfk_1` FOREIGN KEY (`modomics_id`) REFERENCES `modomics` (`id`)
)

CREATE TABLE `modomics` (
  `id` varchar(128) NOT NULL,
  `name` varchar(255) NOT NULL,
  `short_name` varchar(32) NOT NULL,
  `moiety` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
)

CREATE TABLE `ncbi_taxa` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL,
  `taxonomy_id` int(11) NOT NULL,
  `short_name` varchar(128) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `taxonomy_id` (`taxonomy_id`),
  CONSTRAINT `ncbi_taxa_ibfk_1` FOREIGN KEY (`taxonomy_id`) REFERENCES `taxonomy` (`id`)
)

CREATE TABLE `organism` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cto` varchar(255) NOT NULL,
  `taxa_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `taxa_id` (`taxa_id`),
  CONSTRAINT `organism_ibfk_1` FOREIGN KEY (`taxa_id`) REFERENCES `ncbi_taxa` (`id`)
)

CREATE TABLE `project` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `summary` text NOT NULL,
  `contact_name` varchar(128) NOT NULL,
  `contact_institution` varchar(255) NOT NULL,
  `contact_email` varchar(320) NOT NULL,
  `date_published` datetime NOT NULL,
  `date_added` datetime NOT NULL,
  `doi` varchar(255) DEFAULT NULL,
  `pmid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

CREATE TABLE `selection` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `modification_id` int(11) NOT NULL,
  `technology_id` int(11) NOT NULL,
  `organism_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `modification_id` (`modification_id`,`technology_id`,`organism_id`),
  KEY `technology_id` (`technology_id`),
  KEY `organism_id` (`organism_id`),
  CONSTRAINT `selection_ibfk_1` FOREIGN KEY (`modification_id`) REFERENCES `modification` (`id`),
  CONSTRAINT `selection_ibfk_2` FOREIGN KEY (`technology_id`) REFERENCES `technology` (`id`),
  CONSTRAINT `selection_ibfk_3` FOREIGN KEY (`organism_id`) REFERENCES `organism` (`id`)
)

CREATE TABLE `taxonomy` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `domain` varchar(32) NOT NULL,
  `kingdom` varchar(32) DEFAULT NULL,
  `phylum` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

CREATE TABLE `technology` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tech` varchar(255) NOT NULL,
  `method_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `method_id` (`method_id`),
  CONSTRAINT `technology_ibfk_1` FOREIGN KEY (`method_id`) REFERENCES `method` (`id`)
)