# Description of Schema

## Tables

organism
organism_dbxref
organismprop
organismprop_pub
organism_pub
organism_cvterm
organism_cvtermprop
organism_relationship

### organism

Top

Comments:
$Id: organism.sql,v 1.19 2007/04/01 18:45:41 briano Exp $
==========================================
Chado organism module
============
DEPENDENCIES
============
:import cvterm from cv
:import dbxref from db
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
================================================
TABLE: organism
================================================
The organismal taxonomic classification. Note that phylogenies are represented using the phylogeny module, and taxonomies can be represented using the cvterm module or the phylogeny module.

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
organism_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
abbreviation | varchar | 255 | NULL |  |
genus | varchar | 255 |  | UNIQUE, NOT NULL |

species

varchar

255

UNIQUE, NOT NULL, A type of organism is always uniquely identified by genus and species. When mapping from the NCBI taxonomy names.dmp file, this column must be used where it is present, as the common_name column is not always unique (e.g. environmental samples). If a particular strain or subspecies is to be represented, this is appended onto the species name. Follows standard NCBI taxonomy pattern.

common_name | varchar | 255 | NULL |  |
infraspecific_name | varchar | 1024 | NULL | UNIQUE, The scientific name for any taxon below the rank of species. The rank should be specified using the type_id field and the name is provided here. |
type_id | integer | 20 | null | UNIQUE, A controlled vocabulary term that specifies the organism rank below species. It is used when an infraspecific name is provided. Ideally, the rank should be a valid ICN name such as subspecies, varietas, subvarietas, forma and subforma | cvterm.cvterm_id
comment | text | 64000 | NULL |  |

### Constraints

Type | Fields
--- | ---
NOT NULL | organism_id
NOT NULL | genus
NOT NULL | species
FOREIGN KEY | type_id
UNIQUE | genus, species, type_id, infraspecific_name

### organism_dbxref

Top

Comments:
================================================
TABLE: organism_dbxref
================================================
Links an organism to a dbxref.

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
organism_dbxref_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
organism_id | integer | 20 |  | UNIQUE, NOT NULL | organism.organism_id
dbxref_id | integer | 20 |  | UNIQUE, NOT NULL | dbxref.dbxref_id

### Indices

Name | Fields
--- | ---
organism_dbxref_idx1 | organism_id
organism_dbxref_idx2 | dbxref_id

### Constraints

Type | Fields
--- | ---
NOT NULL | organism_dbxref_id
NOT NULL | organism_id
FOREIGN KEY | organism_id
NOT NULL | dbxref_id
FOREIGN KEY | dbxref_id
UNIQUE | organism_id, dbxref_id

### organismprop

Top

Comments:
================================================
TABLE: organismprop
================================================
Tag-value properties - follows standard chado model.

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
organismprop_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
organism_id | integer | 20 |  | UNIQUE, NOT NULL | organism.organism_id
type_id | integer | 20 |  | UNIQUE, NOT NULL | cvterm.cvterm_id
value | text | 64000 | NULL |  |
rank | integer | 10 | 0 | UNIQUE, NOT NULL |

### Indices

Name | Fields
--- | ---
organismprop_idx1 | organism_id
organismprop_idx2 | type_id

### Constraints

Type | Fields
--- | ---
NOT NULL | organismprop_id
NOT NULL | organism_id
FOREIGN KEY | organism_id
NOT NULL | type_id
FOREIGN KEY | type_id
NOT NULL | rank
UNIQUE | organism_id, type_id, rank

### organismprop_pub

Top

Comments:
================================================
TABLE: organismprop_pub
================================================
Attribution for organismprop.

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
organismprop_pub_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
organismprop_id | integer | 20 |  | UNIQUE, NOT NULL | organismprop.organismprop_id
pub_id | integer | 20 |  | UNIQUE, NOT NULL | pub.pub_id
value | text | 64000 | NULL |  |
rank | integer | 10 | 0 | NOT NULL |

### Indices

Name | Fields
--- | ---
organismprop_pub_idx1 | organismprop_id
organismprop_pub_idx2 | pub_id

### Constraints

Type | Fields
--- | ---
NOT NULL | organismprop_pub_id
NOT NULL | organismprop_id
FOREIGN KEY | organismprop_id
NOT NULL | pub_id
FOREIGN KEY | pub_id
NOT NULL | rank
UNIQUE | organismprop_id, pub_id

### organism_pub

Top

Comments:
================================================
TABLE: organism_pub
================================================
Attribution for organism.

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
organism_pub_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
organism_id | integer | 20 |  | UNIQUE, NOT NULL | organism.organism_id
pub_id | integer | 20 |  | UNIQUE, NOT NULL | pub.pub_id

### Indices

Name | Fields
--- | ---
organism_pub_idx1 | organism_id
organism_pub_idx2 | pub_id

### Constraints

Type | Fields
--- | ---
NOT NULL | organism_pub_id
NOT NULL | organism_id
FOREIGN KEY | organism_id
NOT NULL | pub_id
FOREIGN KEY | pub_id
UNIQUE | organism_id, pub_id

### organism_cvterm

Top

Comments:
================================================
TABLE: organism_cvterm
================================================
organism to cvterm associations. Examples: taxonomic name

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
organism_cvterm_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
organism_id | integer | 20 |  | UNIQUE, NOT NULL | organism.organism_id
cvterm_id | integer | 20 |  | UNIQUE, NOT NULL | cvterm.cvterm_id
rank | integer | 10 | 0 | NOT NULL, Property-Value ordering. Any organism_cvterm 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 |
pub_id | integer | 20 |  | UNIQUE, NOT NULL | pub.pub_id

### Indices

Name | Fields
--- | ---
organism_cvterm_idx1 | organism_id
organism_cvterm_idx2 | cvterm_id

### Constraints

Type | Fields
--- | ---
NOT NULL | organism_cvterm_id
NOT NULL | organism_id
FOREIGN KEY | organism_id
NOT NULL | cvterm_id
FOREIGN KEY | cvterm_id
NOT NULL | rank
NOT NULL | pub_id
FOREIGN KEY | pub_id
UNIQUE | organism_id, cvterm_id, pub_id

### organism_cvtermprop

Top

Comments:
================================================
TABLE: organism_cvtermprop
================================================
Extensible properties for organism to cvterm associations. Examples: qualifiers

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
organism_cvtermprop_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
organism_cvterm_id | integer | 20 |  | UNIQUE, NOT NULL | organism_cvterm.organism_cvterm_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 organism_cvterm 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
--- | ---
organism_cvtermprop_idx1 | organism_cvterm_id
organism_cvtermprop_idx2 | type_id

### Constraints

Type | Fields
--- | ---
NOT NULL | organism_cvtermprop_id
NOT NULL | organism_cvterm_id
FOREIGN KEY | organism_cvterm_id
NOT NULL | type_id
FOREIGN KEY | type_id
NOT NULL | rank
UNIQUE | organism_cvterm_id, type_id, rank

### organism_relationship

Top

Comments:
================================================
TABLE: organism_relationship
================================================
Specifies relationships between organisms that are not taxonomic. For example, in breeding, relationships such as "sterile_with", "incompatible_with", or "fertile_with" would be appropriate. Taxonomic relatinoships should be housed in the phylogeny tables.

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
organism_relationship_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
subject_id | integer | 20 |  | UNIQUE, NOT NULL | organism.organism_id
object_id | integer | 20 |  | UNIQUE, NOT NULL | organism.organism_id
type_id | integer | 20 |  | UNIQUE, NOT NULL | cvterm.cvterm_id
rank | integer | 10 | 0 | UNIQUE, NOT NULL |

### Indices

Name | Fields
--- | ---
organism_relationship_idx1 | subject_id
organism_relationship_idx2 | object_id
organism_relationship_idx3 | type_id

### Constraints

Type | Fields
--- | ---
NOT NULL | organism_relationship_id
NOT NULL | subject_id
NOT NULL | object_id
NOT NULL | type_id
NOT NULL | rank
UNIQUE | subject_id, object_id, type_id, rank
FOREIGN KEY | object_id
FOREIGN KEY | subject_id
FOREIGN KEY | type_id

Created by SQL::Translator 0.11020