# Description of Schema

## Tables

phylotree
phylotree_pub
phylotreeprop
phylonode
phylonode_dbxref
phylonode_pub
phylonode_organism
phylonodeprop
phylonode_relationship

### phylotree

Top

Comments:
$Id: phylogeny.sql,v 1.11 2007-04-12 17:00:30 briano Exp $
==========================================
Chado phylogenetics module
Richard Bruskiewich
Chris Mungall
Initial design: 2004-05-27
============
DEPENDENCIES
============
:import feature from sequence
:import cvterm from cv
:import pub from pub
:import organism from organism
:import dbxref from db
:import analysis from companalysis
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
================================================
TABLE: phylotree
================================================
Global anchor for phylogenetic tree.

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
phylotree_id | integer | 20 |  | PRIMARY KEY, UNIQUE, NOT NULL |
dbxref_id | integer | 20 |  | NOT NULL | dbxref.dbxref_id
name | varchar | 255 | NULL |  |
type_id | integer | 20 |  | Type: protein, nucleotide, taxonomy, for example. The type should be any SO type, or "taxonomy". | cvterm.cvterm_id
analysis_id | integer | 20 | NULL |  | analysis.analysis_id
comment | text | 64000 | NULL |  |

### Indices

Name | Fields
--- | ---
phylotree_idx1 | phylotree_id

### Constraints

Type | Fields
--- | ---
NOT NULL | phylotree_id
NOT NULL | dbxref_id
FOREIGN KEY | dbxref_id
FOREIGN KEY | type_id
FOREIGN KEY | analysis_id
UNIQUE | phylotree_id

### phylotree_pub

Top

Comments:
================================================
TABLE: phylotree_pub
================================================
Tracks citations global to the tree e.g. multiple sequence alignment supporting tree construction.

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
phylotree_pub_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
phylotree_id | integer | 20 |  | UNIQUE, NOT NULL | phylotree.phylotree_id
pub_id | integer | 20 |  | UNIQUE, NOT NULL | pub.pub_id

### Indices

Name | Fields
--- | ---
phylotree_pub_idx1 | phylotree_id
phylotree_pub_idx2 | pub_id

### Constraints

Type | Fields
--- | ---
NOT NULL | phylotree_pub_id
NOT NULL | phylotree_id
FOREIGN KEY | phylotree_id
NOT NULL | pub_id
FOREIGN KEY | pub_id
UNIQUE | phylotree_id, pub_id

### phylotreeprop

Top

Comments:
================================================
TABLE: phylotreeprop
================================================
A phylotree can have any number of slot-value property tags attached to it. This is an alternative to hardcoding a list of columns in the relational schema, and is completely extensible.

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
phylotreeprop_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
phylotree_id | integer | 20 |  | UNIQUE, NOT NULL | phylotree.phylotree_id
type_id | integer | 20 |  | UNIQUE, NOT NULL, The name of the property/slot is a cvterm. The meaning of the property is defined in that cvterm. | cvterm.cvterm_id
value | text | 64000 | NULL | The value of the property, represented as text. Numeric values are converted to their text representation. This is less efficient than using native database types, but is easier to query. |
rank | integer | 10 | 0 | UNIQUE, NOT NULL, Property-Value ordering. Any phylotree can have multiple values for any particular property type these are ordered in a list using rank, counting from zero. For properties that are single-valued rather than multi-valued, the default 0 value should be used |

### Indices

Name | Fields
--- | ---
phylotreeprop_idx1 | phylotree_id
phylotreeprop_idx2 | type_id

### Constraints

Type | Fields
--- | ---
NOT NULL | phylotreeprop_id
NOT NULL | phylotree_id
NOT NULL | type_id
NOT NULL | rank
FOREIGN KEY | phylotree_id
FOREIGN KEY | type_id
UNIQUE | phylotree_id, type_id, rank

### phylonode

Top

Comments:
For any one phylotree, multivalued property-value pairs must be differentiated by rank.
================================================
TABLE: phylonode
================================================
Bootstrap float null.
This is the most pervasive element in the phylogeny module, cataloging the "phylonodes" of tree graphs. Edges are implied by the parent_phylonode_id reflexive closure. For all nodes in a nested set implementation the left and right index will be *between* the parents left and right indexes.

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
phylonode_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
phylotree_id | integer | 20 |  | UNIQUE, NOT NULL | phylotree.phylotree_id
parent_phylonode_id | integer | 20 | NULL | Root phylonode can have null parent_phylonode_id value. | phylonode.phylonode_id
left_idx | integer | 10 |  | UNIQUE, NOT NULL |
right_idx | integer | 10 |  | UNIQUE, NOT NULL |
type_id | integer | 20 |  | Type: e.g. root, interior, leaf. | cvterm.cvterm_id
feature_id | integer | 20 |  | Phylonodes can have optional features attached to them e.g. a protein or nucleotide sequence usually attached to a leaf of the phylotree for non-leaf nodes, the feature may be a feature that is an instance of SO:match; this feature is the alignment of all leaf features beneath it. | feature.feature_id
label | varchar | 255 | NULL |  |
distance | float | 20 | NULL |  |

### Indices

Name | Fields
--- | ---
phylonode_parent_phylonode_id_idx | parent_phylonode_id

### Constraints

Type | Fields
--- | ---
NOT NULL | phylonode_id
NOT NULL | phylotree_id
FOREIGN KEY | phylotree_id
FOREIGN KEY | parent_phylonode_id
NOT NULL | left_idx
NOT NULL | right_idx
FOREIGN KEY | type_id
FOREIGN KEY | feature_id
UNIQUE | phylotree_id, left_idx
UNIQUE | phylotree_id, right_idx

### phylonode_dbxref

Top

Comments:
================================================
TABLE: phylonode_dbxref
================================================
For example, for orthology, paralogy group identifiers; could also be used for NCBI taxonomy; for sequences, refer to phylonode_feature, feature associated dbxrefs.

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
phylonode_dbxref_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
phylonode_id | integer | 20 |  | UNIQUE, NOT NULL | phylonode.phylonode_id
dbxref_id | integer | 20 |  | UNIQUE, NOT NULL | dbxref.dbxref_id

### Indices

Name | Fields
--- | ---
phylonode_dbxref_idx1 | phylonode_id
phylonode_dbxref_idx2 | dbxref_id

### Constraints

Type | Fields
--- | ---
NOT NULL | phylonode_dbxref_id
NOT NULL | phylonode_id
FOREIGN KEY | phylonode_id
NOT NULL | dbxref_id
FOREIGN KEY | dbxref_id
UNIQUE | phylonode_id, dbxref_id

### phylonode_pub

Top

Comments:
================================================
TABLE: phylonode_pub
================================================

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
phylonode_pub_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
phylonode_id | integer | 20 |  | UNIQUE, NOT NULL | phylonode.phylonode_id
pub_id | integer | 20 |  | UNIQUE, NOT NULL | pub.pub_id

### Indices

Name | Fields
--- | ---
phylonode_pub_idx1 | phylonode_id
phylonode_pub_idx2 | pub_id

### Constraints

Type | Fields
--- | ---
NOT NULL | phylonode_pub_id
NOT NULL | phylonode_id
FOREIGN KEY | phylonode_id
NOT NULL | pub_id
FOREIGN KEY | pub_id
UNIQUE | phylonode_id, pub_id

### phylonode_organism

Top

Comments:
================================================
TABLE: phylonode_organism
================================================
This linking table should only be used for nodes in taxonomy trees; it provides a mapping between the node and an organism. One node can have zero or one organisms, one organism can have zero or more nodes (although typically it should only have one in the standard NCBI taxonomy tree).

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
phylonode_organism_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
phylonode_id | integer | 20 |  | UNIQUE, NOT NULL, One phylonode cannot refer to >1 organism. | phylonode.phylonode_id
organism_id | integer | 20 |  | NOT NULL | organism.organism_id

### Indices

Name | Fields
--- | ---
phylonode_organism_idx1 | phylonode_id
phylonode_organism_idx2 | organism_id

### Constraints

Type | Fields
--- | ---
NOT NULL | phylonode_organism_id
NOT NULL | phylonode_id
FOREIGN KEY | phylonode_id
NOT NULL | organism_id
FOREIGN KEY | organism_id
UNIQUE | phylonode_id

### phylonodeprop

Top

Comments:
================================================
TABLE: phylonodeprop
================================================

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
phylonodeprop_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
phylonode_id | integer | 20 |  | UNIQUE, NOT NULL | phylonode.phylonode_id
type_id | integer | 20 |  | UNIQUE, NOT NULL, type_id could designate phylonode hierarchy relationships, for example: species taxonomy (kingdom, order, family, genus, species), "ortholog/paralog", "fold/superfold", etc. | cvterm.cvterm_id
value | text | 64000 |  | UNIQUE, NOT NULL |
rank | integer | 10 | 0 | UNIQUE, NOT NULL, It is not clear how useful the rank concept is here, leave it in for now. |

### Indices

Name | Fields
--- | ---
phylonodeprop_idx1 | phylonode_id
phylonodeprop_idx2 | type_id

### Constraints

Type | Fields
--- | ---
NOT NULL | phylonodeprop_id
NOT NULL | phylonode_id
FOREIGN KEY | phylonode_id
NOT NULL | type_id
FOREIGN KEY | type_id
NOT NULL | value
NOT NULL | rank
UNIQUE | phylonode_id, type_id, value, rank

### phylonode_relationship

Top

Comments:
================================================
TABLE: phylonode_relationship
================================================
This is for relationships that are not strictly hierarchical; for example, horizontal gene transfer. Most phylogenetic trees are strictly hierarchical, nevertheless it is here for completeness.

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
phylonode_relationship_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
subject_id | integer | 20 |  | UNIQUE, NOT NULL | phylonode.phylonode_id
object_id | integer | 20 |  | UNIQUE, NOT NULL | phylonode.phylonode_id
type_id | integer | 20 |  | UNIQUE, NOT NULL | cvterm.cvterm_id
rank | integer | 10 |  |  |
phylotree_id | integer | 20 |  | NOT NULL | phylotree.phylotree_id

### Indices

Name | Fields
--- | ---
phylonode_relationship_idx1 | subject_id
phylonode_relationship_idx2 | object_id
phylonode_relationship_idx3 | type_id

### Constraints

Type | Fields
--- | ---
NOT NULL | phylonode_relationship_id
NOT NULL | subject_id
FOREIGN KEY | subject_id
NOT NULL | object_id
FOREIGN KEY | object_id
NOT NULL | type_id
FOREIGN KEY | type_id
NOT NULL | phylotree_id
FOREIGN KEY | phylotree_id
UNIQUE | subject_id, object_id, type_id

Created by SQL::Translator 0.11020