Difference between revisions of "Chado Post-Composed Phenotypes"
From GMOD
(Created page with "== Overview == Increasingly phenotypes are rarely indicated with single, pre-composed term. A particular phenotype (or phene) can be described with an EAV statement (entity-a...") |
|||
| Line 2: | Line 2: | ||
Increasingly phenotypes are rarely indicated with single, pre-composed term. A particular phenotype (or phene) can be described with an EAV statement (entity-attribute-value), or perhaps with more complex EQ statements (Entity-Quality statements in which the Entity and Quality parts themselves may contain several terms), and one expects even more complex statements in the future. In addition to containing multiple terms, these statements have a specific syntax that is critical to the meaning of the statement. | Increasingly phenotypes are rarely indicated with single, pre-composed term. A particular phenotype (or phene) can be described with an EAV statement (entity-attribute-value), or perhaps with more complex EQ statements (Entity-Quality statements in which the Entity and Quality parts themselves may contain several terms), and one expects even more complex statements in the future. In addition to containing multiple terms, these statements have a specific syntax that is critical to the meaning of the statement. | ||
| + | |||
| + | Our goal was to make minimal changes to Chado, and some of those are in the form of recommending deprecating some exiting table fields. | ||
The proposal below makes use of the newly-proposed Group Module (in 2014) to show syntax structure, and some new proposed tables to be added to the Phenotype Module. | The proposal below makes use of the newly-proposed Group Module (in 2014) to show syntax structure, and some new proposed tables to be added to the Phenotype Module. | ||
| Line 7: | Line 9: | ||
== Example == | == Example == | ||
| − | An example of an EQ statement and how it could be stored in the proposed revised Phenotype Module: | + | An example of an EQ statement and how it could be stored in the proposed revised Phenotype Module is below. New tables are indicated in green. |
| + | [[File:SampleEQstatementInChado.png]] | ||
| + | |||
| + | == New Tables == | ||
| + | |||
| + | CREATE TABLE phenotypeprop ( | ||
| + | phenotypeprop_id SERIAL PRIMARY KEY, | ||
| + | phenotype_id INT NOT NULL, | ||
| + | FOREIGN KEY (phenotype_id) REFERENCES phenotype (phenotype_id) ON DELETE CASCADE INITIALLY DEFERRED, | ||
| + | type_id INT NOT NULL, | ||
| + | FOREIGN KEY (type_id) REFERENCES cvterm (cvterm_id) ON DELETE CASCADE INITIALLY DEFERRED, | ||
| + | value TEXT NULL, | ||
| + | rank INT NOT NULL DEFAULT 0, | ||
| + | |||
| + | CONSTRAINT phenotypeprop_c1 UNIQUE (phenotypeprop_id,type_id,rank) | ||
| + | ); | ||
| + | |||
| + | CREATE TABLE phenotype_cvterm ( | ||
| + | phenotype_cvterm_id SERIAL PRIMARY KEY, | ||
| + | phenotype_id INT NOT NULL, | ||
| + | FOREIGN KEY (phenotype_id) REFERENCES phenotype (phenotype_id) ON DELETE CASCADE INITIALLY DEFERRED, | ||
| + | cvterm_id INT NOT NULL, | ||
| + | FOREIGN KEY (type_id) REFERENCES cvterm (cvterm_id) ON DELETE CASCADE INITIALLY DEFERRED, | ||
| + | type_id INT NOT NULL, | ||
| + | FOREIGN KEY (type_id) REFERENCES cvterm (cvterm_id) ON DELETE CASCADE INITIALLY DEFERRED, | ||
| + | grp_id INT NOT NULL, | ||
| + | FOREIGN KEY (grp_id) REFERENCES grp (grp_id) ON DELETE CASCADE INITIALLY DEFERRED, | ||
| + | rank INT NOT NULL DEFAULT 0, | ||
| + | |||
| + | CONSTRAINT phenotype_cvterm_c1 UNIQUE (phenotype_id,type_id,rank) | ||
| + | ); | ||
Revision as of 13:51, 20 January 2015
Overview
Increasingly phenotypes are rarely indicated with single, pre-composed term. A particular phenotype (or phene) can be described with an EAV statement (entity-attribute-value), or perhaps with more complex EQ statements (Entity-Quality statements in which the Entity and Quality parts themselves may contain several terms), and one expects even more complex statements in the future. In addition to containing multiple terms, these statements have a specific syntax that is critical to the meaning of the statement.
Our goal was to make minimal changes to Chado, and some of those are in the form of recommending deprecating some exiting table fields.
The proposal below makes use of the newly-proposed Group Module (in 2014) to show syntax structure, and some new proposed tables to be added to the Phenotype Module.
Example
An example of an EQ statement and how it could be stored in the proposed revised Phenotype Module is below. New tables are indicated in green.
New Tables
CREATE TABLE phenotypeprop (
phenotypeprop_id SERIAL PRIMARY KEY,
phenotype_id INT NOT NULL,
FOREIGN KEY (phenotype_id) REFERENCES phenotype (phenotype_id) ON DELETE CASCADE INITIALLY DEFERRED,
type_id INT NOT NULL,
FOREIGN KEY (type_id) REFERENCES cvterm (cvterm_id) ON DELETE CASCADE INITIALLY DEFERRED,
value TEXT NULL,
rank INT NOT NULL DEFAULT 0,
CONSTRAINT phenotypeprop_c1 UNIQUE (phenotypeprop_id,type_id,rank)
);
CREATE TABLE phenotype_cvterm (
phenotype_cvterm_id SERIAL PRIMARY KEY,
phenotype_id INT NOT NULL,
FOREIGN KEY (phenotype_id) REFERENCES phenotype (phenotype_id) ON DELETE CASCADE INITIALLY DEFERRED,
cvterm_id INT NOT NULL,
FOREIGN KEY (type_id) REFERENCES cvterm (cvterm_id) ON DELETE CASCADE INITIALLY DEFERRED,
type_id INT NOT NULL,
FOREIGN KEY (type_id) REFERENCES cvterm (cvterm_id) ON DELETE CASCADE INITIALLY DEFERRED,
grp_id INT NOT NULL,
FOREIGN KEY (grp_id) REFERENCES grp (grp_id) ON DELETE CASCADE INITIALLY DEFERRED,
rank INT NOT NULL DEFAULT 0,
CONSTRAINT phenotype_cvterm_c1 UNIQUE (phenotype_id,type_id,rank)
);
