# Description of Schema

## Tables

feature
featureloc
featureloc_pub
feature_pub
feature_pubprop
featureprop
featureprop_pub
feature_dbxref
feature_relationship
feature_relationship_pub
feature_relationshipprop
feature_relationshipprop_pub
feature_cvterm
feature_cvtermprop
feature_cvterm_dbxref
feature_cvterm_pub
synonym
feature_synonym
feature_contact

### feature

Top

Comments:
$Id: sequence.sql,v 1.69 2009-05-14 02:44:23 scottcain Exp $
==========================================
Chado sequence module
=================================================================
Dependencies:
:import cvterm from cv
:import pub from pub
:import organism from organism
:import dbxref from db
:import contact from contact
=================================================================
================================================
TABLE: feature
================================================
A feature is a biological sequence or a section of a biological sequence, or a collection of such sections. Examples include genes, exons, transcripts, regulatory regions, polypeptides, protein domains, chromosome sequences, sequence variations, cross-genome match regions such as hits and HSPs and so on; see the Sequence Ontology for more. The combination of organism_id, uniquename and type_id should be unique.

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
feature_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
dbxref_id | integer | 20 |  | An optional primary public stable identifier for this feature. Secondary identifiers and external dbxrefs go in the table feature_dbxref. | dbxref.dbxref_id
organism_id | integer | 20 |  | UNIQUE, NOT NULL, The organism to which this feature belongs. This column is mandatory. | organism.organism_id
name | varchar | 255 |  | The optional human-readable common name for a feature, for display purposes. |
uniquename | text | 64000 |  | UNIQUE, NOT NULL, The unique name for a feature; may not be necessarily be particularly human-readable, although this is preferred. This name must be unique for this type of feature within this organism. |

residues

text

64000

A sequence of alphabetic characters representing biological residues (nucleic acids, amino acids). This column does not need to be manifested for all features; it is optional for features such as exons where the residues can be derived from the featureloc. It is recommended that the value for this column be manifested for features which may may non-contiguous sublocations (e.g. transcripts), since derivation at query time is non-trivial. For expressed sequence, the DNA sequence should be used rather than the RNA sequence. The default storage method for the residues column is EXTERNAL, which will store it uncompressed to make substring operations faster.

seqlen

integer

20

The length of the residue feature. See column:residues. This column is partially redundant with the residues column, and also with featureloc. This column is required because the location may be unknown and the residue sequence may not be manifested, yet it may be desirable to store and query the length of the feature. The seqlen should always be manifested where the length of the sequence is known.

md5checksum | char | 32 |  | The 32-character checksum of the sequence, calculated using the MD5 algorithm. This is practically guaranteed to be unique for any feature. This column thus acts as a unique identifier on the mathematical sequence. |
type_id | integer | 20 |  | UNIQUE, NOT NULL, A required reference to a table:cvterm giving the feature type. This will typically be a Sequence Ontology identifier. This column is thus used to subclass the feature table. | cvterm.cvterm_id

is_analysis

boolean

0

false

NOT NULL, Boolean indicating whether this feature is annotated or the result of an automated analysis. Analysis results also use the companalysis module. Note that the dividing line between analysis and annotation may be fuzzy, this should be determined on a per-project basis in a consistent manner. One requirement is that there should only be one non-analysis version of each wild-type gene feature in a genome, whereas the same gene feature can be predicted multiple times in different analyses.

is_obsolete | boolean | 0 | false | NOT NULL, Boolean indicating whether this feature has been obsoleted. Some chado instances may choose to simply remove the feature altogether, others may choose to keep an obsolete row in the table. |
timeaccessioned | timestamp | 0 | current_timestamp | NOT NULL, For handling object accession or modification timestamps (as opposed to database auditing data, handled elsewhere). The expectation is that these fields would be available to software interacting with chado. |
timelastmodified | timestamp | 0 | current_timestamp | NOT NULL, For handling object accession or modification timestamps (as opposed to database auditing data, handled elsewhere). The expectation is that these fields would be available to software interacting with chado. |

### Indices

Name | Fields
--- | ---
feature_name_ind1 | name
feature_idx1 | dbxref_id
feature_idx2 | organism_id
feature_idx3 | type_id
feature_idx4 | uniquename

### Constraints

Type | Fields
--- | ---
NOT NULL | feature_id
FOREIGN KEY | dbxref_id
NOT NULL | organism_id
FOREIGN KEY | organism_id
NOT NULL | uniquename
NOT NULL | type_id
FOREIGN KEY | type_id
NOT NULL | is_analysis
NOT NULL | is_obsolete
NOT NULL | timeaccessioned
NOT NULL | timelastmodified
UNIQUE | organism_id, uniquename, type_id

### featureloc

Top

Comments:
COMMENT ON INDEX feature_c1 IS 'Any feature can be globally identified
by the combination of organism, uniquename and feature type';
================================================
TABLE: featureloc
================================================
The location of a feature relative to another feature. Important: interbase coordinates are used. This is vital as it allows us to represent zero-length features e.g. splice sites, insertion points without an awkward fuzzy system. Features typically have exactly ONE location, but this need not be the case. Some features may not be localized (e.g. a gene that has been characterized genetically but no sequence or molecular information is available). Note on multiple locations: Each feature can have 0 or more locations. Multiple locations do NOT indicate non-contiguous locations (if a feature such as a transcript has a non-contiguous location, then the subfeatures such as exons should always be manifested). Instead, multiple featurelocs for a feature designate alternate locations or grouped locations; for instance, a feature designating a blast hit or hsp will have two locations, one on the query feature, one on the subject feature. Features representing sequence variation could have alternate locations instantiated on a feature on the mutant strain. The column:rank is used to differentiate these different locations. Reflexive locations should never be stored - this is for -proper- (i.e. non-self) locations only; nothing should be located relative to itself.

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
featureloc_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
feature_id | integer | 20 |  | UNIQUE, NOT NULL, The feature that is being located. Any feature can have zero or more featurelocs. | feature.feature_id
srcfeature_id | integer | 20 |  | The source feature which this location is relative to. Every location is relative to another feature (however, this column is nullable, because the srcfeature may not be known). All locations are -proper- that is, nothing should be located relative to itself. No cycles are allowed in the featureloc graph. | feature.feature_id

fmin

integer

20

The leftmost/minimal boundary in the linear range represented by the featureloc. Sometimes (e.g. in Bioperl) this is called -start- although this is confusing because it does not necessarily represent the 5-prime coordinate. Important: This is space-based (interbase) coordinates, counting from zero. To convert this to the leftmost position in a base-oriented system (eg GFF, Bioperl), add 1 to fmin.

is_fmin_partial | boolean | 0 | false | NOT NULL, This is typically false, but may be true if the value for column:fmin is inaccurate or the leftmost part of the range is unknown/unbounded. |

fmax

integer

20

The rightmost/maximal boundary in the linear range represented by the featureloc. Sometimes (e.g. in bioperl) this is called -end- although this is confusing because it does not necessarily represent the 3-prime coordinate. Important: This is space-based (interbase) coordinates, counting from zero. No conversion is required to go from fmax to the rightmost coordinate in a base-oriented system that counts from 1 (e.g. GFF, Bioperl).

is_fmax_partial | boolean | 0 | false | NOT NULL, This is typically false, but may be true if the value for column:fmax is inaccurate or the rightmost part of the range is unknown/unbounded. |
strand | integer | 5 |  | The orientation/directionality of the location. Should be 0, -1 or +1. |
phase | integer | 10 |  | Phase of translation with respect to srcfeature_id. Values are 0, 1, 2. It may not be possible to manifest this column for some features such as exons, because the phase is dependant on the spliceform (the same exon can appear in multiple spliceforms). This column is mostly useful for predicted exons and CDSs. |

residue_info

text

64000

Alternative residues, when these differ from feature.residues. For instance, a SNP feature located on a wild and mutant protein would have different alternative residues. for alignment/similarity features, the alternative residues is used to represent the alignment string (CIGAR format). Note on variation features; even if we do not want to instantiate a mutant chromosome/contig feature, we can still represent a SNP etc with 2 locations, one (rank 0) on the genome, the other (rank 1) would have most fields null, except for alternative residues.

locgroup

integer

10

0

UNIQUE, NOT NULL, This is used to manifest redundant, derivable extra locations for a feature. The default locgroup=0 is used for the DIRECT location of a feature. Important: most Chado users may never use featurelocs WITH logroup > 0. Transitively derived locations are indicated with locgroup > 0. For example, the position of an exon on a BAC and in global chromosome coordinates. This column is used to differentiate these groupings of locations. The default locgroup 0 is used for the main or primary location, from which the others can be derived via coordinate transformations. Another example of redundant locations is storing ORF coordinates relative to both transcript and genome. Redundant locations open the possibility of the database getting into inconsistent states; this schema gives us the flexibility of both warehouse instantiations with redundant locations (easier for querying) and management instantiations with no redundant locations. An example of using both locgroup and rank: imagine a feature indicating a conserved region between the chromosomes of two different species. We may want to keep redundant locations on both contigs and chromosomes. We would thus have 4 locations for the single conserved region feature - two distinct locgroups (contig level and chromosome level) and two distinct ranks (for the two species).

rank

integer

10

0

UNIQUE, NOT NULL, Used when a feature has >1 location, otherwise the default rank 0 is used. Some features (e.g. blast hits and HSPs) have two locations - one on the query and one on the subject. Rank is used to differentiate these. Rank=0 is always used for the query, Rank=1 for the subject. For multiple alignments, assignment of rank is arbitrary. Rank is also used for sequence_variant features, such as SNPs. Rank=0 indicates the wildtype (or baseline) feature, Rank=1 indicates the mutant (or compared) feature.

### Indices

Name | Fields
--- | ---
featureloc_idx1 | feature_id
featureloc_idx2 | srcfeature_id
featureloc_idx3 | srcfeature_id, fmin, fmax

### Constraints

Type | Fields
--- | ---
NOT NULL | featureloc_id
NOT NULL | feature_id
FOREIGN KEY | feature_id
FOREIGN KEY | srcfeature_id
NOT NULL | is_fmin_partial
NOT NULL | is_fmax_partial
NOT NULL | locgroup
NOT NULL | rank
UNIQUE | feature_id, locgroup, rank
CHECK |

### featureloc_pub

Top

Comments:
COMMENT ON INDEX featureloc_c1 IS 'locgroup and rank serve to uniquely
partition locations for any one feature';
================================================
TABLE: featureloc_pub
================================================
Provenance of featureloc. Linking table between featurelocs and publications that mention them.

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
featureloc_pub_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
featureloc_id | integer | 20 |  | UNIQUE, NOT NULL | featureloc.featureloc_id
pub_id | integer | 20 |  | UNIQUE, NOT NULL | pub.pub_id

### Indices

Name | Fields
--- | ---
featureloc_pub_idx1 | featureloc_id
featureloc_pub_idx2 | pub_id

### Constraints

Type | Fields
--- | ---
NOT NULL | featureloc_pub_id
NOT NULL | featureloc_id
FOREIGN KEY | featureloc_id
NOT NULL | pub_id
FOREIGN KEY | pub_id
UNIQUE | featureloc_id, pub_id

### feature_pub

Top

Comments:
================================================
TABLE: feature_pub
================================================
Provenance. Linking table between features and publications that mention them.

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
feature_pub_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
feature_id | integer | 20 |  | UNIQUE, NOT NULL | feature.feature_id
pub_id | integer | 20 |  | UNIQUE, NOT NULL | pub.pub_id

### Indices

Name | Fields
--- | ---
feature_pub_idx1 | feature_id
feature_pub_idx2 | pub_id

### Constraints

Type | Fields
--- | ---
NOT NULL | feature_pub_id
NOT NULL | feature_id
FOREIGN KEY | feature_id
NOT NULL | pub_id
FOREIGN KEY | pub_id
UNIQUE | feature_id, pub_id

### feature_pubprop

Top

Comments:
================================================
TABLE: feature_pubprop
================================================
Property or attribute of a feature_pub link.

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
feature_pubprop_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
feature_pub_id | integer | 20 |  | UNIQUE, NOT NULL | feature_pub.feature_pub_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
--- | ---
feature_pubprop_idx1 | feature_pub_id

### Constraints

Type | Fields
--- | ---
NOT NULL | feature_pubprop_id
NOT NULL | feature_pub_id
FOREIGN KEY | feature_pub_id
NOT NULL | type_id
FOREIGN KEY | type_id
NOT NULL | rank
UNIQUE | feature_pub_id, type_id, rank

### featureprop

Top

Comments:
================================================
TABLE: featureprop
================================================
A feature 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
--- | --- | --- | --- | --- | ---
featureprop_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
feature_id | integer | 20 |  | UNIQUE, NOT NULL | feature.feature_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. Certain property types will only apply to certain feature types (e.g. the anticodon property will only apply to tRNA features) ; the types here come from the sequence feature property ontology. | 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 feature 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
--- | ---
featureprop_idx1 | feature_id
featureprop_idx2 | type_id

### Constraints

Type | Fields
--- | ---
NOT NULL | featureprop_id
NOT NULL | feature_id
FOREIGN KEY | feature_id
NOT NULL | type_id
FOREIGN KEY | type_id
NOT NULL | rank
UNIQUE | feature_id, type_id, rank

### featureprop_pub

Top

Comments:
For any one feature, multivalued property-value pairs must be differentiated by rank.
================================================
TABLE: featureprop_pub
================================================
Provenance. Any featureprop assignment can optionally be supported by a publication.

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
featureprop_pub_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
featureprop_id | integer | 20 |  | UNIQUE, NOT NULL | featureprop.featureprop_id
pub_id | integer | 20 |  | UNIQUE, NOT NULL | pub.pub_id

### Indices

Name | Fields
--- | ---
featureprop_pub_idx1 | featureprop_id
featureprop_pub_idx2 | pub_id

### Constraints

Type | Fields
--- | ---
NOT NULL | featureprop_pub_id
NOT NULL | featureprop_id
FOREIGN KEY | featureprop_id
NOT NULL | pub_id
FOREIGN KEY | pub_id
UNIQUE | featureprop_id, pub_id

### feature_dbxref

Top

Comments:
================================================
TABLE: feature_dbxref
================================================
Links a feature to dbxrefs. This is for secondary identifiers; primary identifiers should use feature.dbxref_id.

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
feature_dbxref_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
feature_id | integer | 20 |  | UNIQUE, NOT NULL | feature.feature_id
dbxref_id | integer | 20 |  | UNIQUE, NOT NULL | dbxref.dbxref_id
is_current | boolean | 0 | true | NOT NULL, True if this secondary dbxref is the most up to date accession in the corresponding db. Retired accessions should set this field to false |

### Indices

Name | Fields
--- | ---
feature_dbxref_idx1 | feature_id
feature_dbxref_idx2 | dbxref_id

### Constraints

Type | Fields
--- | ---
NOT NULL | feature_dbxref_id
NOT NULL | feature_id
FOREIGN KEY | feature_id
NOT NULL | dbxref_id
FOREIGN KEY | dbxref_id
NOT NULL | is_current
UNIQUE | feature_id, dbxref_id

### feature_relationship

Top

Comments:
================================================
TABLE: feature_relationship
================================================
Features can be arranged in graphs, e.g. "exon part_of transcript part_of gene"; If type is thought of as a verb, the each arc or edge makes a statement [Subject Verb Object]. The object can also be thought of as parent (containing feature), and subject as child (contained feature or subfeature). We include the relationship rank/order, because even though most of the time we can order things implicitly by sequence coordinates, we can not always do this - e.g. transpliced genes. It is also useful for quickly getting implicit introns.

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
feature_relationship_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
subject_id | integer | 20 |  | UNIQUE, NOT NULL, The subject of the subj-predicate-obj sentence. This is typically the subfeature. | feature.feature_id
object_id | integer | 20 |  | UNIQUE, NOT NULL, The object of the subj-predicate-obj sentence. This is typically the container feature. | feature.feature_id
type_id | integer | 20 |  | UNIQUE, NOT NULL, Relationship type between subject and object. This is a cvterm, typically from the OBO relationship ontology, although other relationship types are allowed. The most common relationship type is OBO_REL:part_of. Valid relationship types are constrained by the Sequence Ontology. | cvterm.cvterm_id
value | text | 64000 | NULL | Additional notes or comments. |
rank | integer | 10 | 0 | UNIQUE, NOT NULL, The ordering of subject features with respect to the object feature may be important (for example, exon ordering on a transcript - not always derivable if you take trans spliced genes into consideration). Rank is used to order these; starts from zero. |

### Indices

Name | Fields
--- | ---
feature_relationship_idx1 | subject_id
feature_relationship_idx2 | object_id
feature_relationship_idx3 | type_id

### Constraints

Type | Fields
--- | ---
NOT NULL | feature_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 | rank
UNIQUE | subject_id, object_id, type_id, rank

### feature_relationship_pub

Top

Comments:
================================================
TABLE: feature_relationship_pub
================================================
Provenance. Attach optional evidence to a feature_relationship in the form of a publication.

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
feature_relationship_pub_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
feature_relationship_id | integer | 20 |  | UNIQUE, NOT NULL | feature_relationship.feature_relationship_id
pub_id | integer | 20 |  | UNIQUE, NOT NULL | pub.pub_id

### Indices

Name | Fields
--- | ---
feature_relationship_pub_idx1 | feature_relationship_id
feature_relationship_pub_idx2 | pub_id

### Constraints

Type | Fields
--- | ---
NOT NULL | feature_relationship_pub_id
NOT NULL | feature_relationship_id
FOREIGN KEY | feature_relationship_id
NOT NULL | pub_id
FOREIGN KEY | pub_id
UNIQUE | feature_relationship_id, pub_id

### feature_relationshipprop

Top

Comments:
================================================
TABLE: feature_relationshipprop
================================================
Extensible properties for feature_relationships. Analagous structure to featureprop. This table is largely optional and not used with a high frequency. Typical scenarios may be if one wishes to attach additional data to a feature_relationship - for example to say that the feature_relationship is only true in certain contexts.

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
feature_relationshipprop_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
feature_relationship_id | integer | 20 |  | UNIQUE, NOT NULL | feature_relationship.feature_relationship_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. Currently there is no standard ontology for feature_relationship property types. | 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 feature_relationship 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
--- | ---
feature_relationshipprop_idx1 | feature_relationship_id
feature_relationshipprop_idx2 | type_id

### Constraints

Type | Fields
--- | ---
NOT NULL | feature_relationshipprop_id
NOT NULL | feature_relationship_id
FOREIGN KEY | feature_relationship_id
NOT NULL | type_id
FOREIGN KEY | type_id
NOT NULL | rank
UNIQUE | feature_relationship_id, type_id, rank

### feature_relationshipprop_pub

Top

Comments:
================================================
TABLE: feature_relationshipprop_pub
================================================
Provenance for feature_relationshipprop.

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
feature_relationshipprop_pub_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
feature_relationshipprop_id | integer | 20 |  | UNIQUE, NOT NULL | feature_relationshipprop.feature_relationshipprop_id
pub_id | integer | 20 |  | UNIQUE, NOT NULL | pub.pub_id

### Indices

Name | Fields
--- | ---
feature_relationshipprop_pub_idx1 | feature_relationshipprop_id
feature_relationshipprop_pub_idx2 | pub_id

### Constraints

Type | Fields
--- | ---
NOT NULL | feature_relationshipprop_pub_id
NOT NULL | feature_relationshipprop_id
FOREIGN KEY | feature_relationshipprop_id
NOT NULL | pub_id
FOREIGN KEY | pub_id
UNIQUE | feature_relationshipprop_id, pub_id

### feature_cvterm

Top

Comments:
================================================
TABLE: feature_cvterm
================================================
Associate a term from a cv with a feature, for example, GO annotation.

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
feature_cvterm_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
feature_id | integer | 20 |  | UNIQUE, NOT NULL | feature.feature_id
cvterm_id | integer | 20 |  | UNIQUE, NOT NULL | cvterm.cvterm_id
pub_id | integer | 20 |  | UNIQUE, NOT NULL, Provenance for the annotation. Each annotation should have a single primary publication (which may be of the appropriate type for computational analyses) where more details can be found. Additional provenance dbxrefs can be attached using feature_cvterm_dbxref. | pub.pub_id
is_not | boolean | 0 | false | NOT NULL, If this is set to true, then this annotation is interpreted as a NEGATIVE annotation - i.e. the feature does NOT have the specified function, process, component, part, etc. See GO docs for more details. |
rank | integer | 10 | 0 | UNIQUE, NOT NULL |

### Indices

Name | Fields
--- | ---
feature_cvterm_idx1 | feature_id
feature_cvterm_idx2 | cvterm_id
feature_cvterm_idx3 | pub_id

### Constraints

Type | Fields
--- | ---
NOT NULL | feature_cvterm_id
NOT NULL | feature_id
FOREIGN KEY | feature_id
NOT NULL | cvterm_id
FOREIGN KEY | cvterm_id
NOT NULL | pub_id
FOREIGN KEY | pub_id
NOT NULL | is_not
NOT NULL | rank
UNIQUE | feature_id, cvterm_id, pub_id, rank

### feature_cvtermprop

Top

Comments:
================================================
TABLE: feature_cvtermprop
================================================
Extensible properties for feature to cvterm associations. Examples: GO evidence codes; qualifiers; metadata such as the date on which the entry was curated and the source of the association. See the featureprop table for meanings of type_id, value and rank.

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
feature_cvtermprop_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
feature_cvterm_id | integer | 20 |  | UNIQUE, NOT NULL | feature_cvterm.feature_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. cvterms may come from the OBO evidence code cv. | 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 feature_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
--- | ---
feature_cvtermprop_idx1 | feature_cvterm_id
feature_cvtermprop_idx2 | type_id

### Constraints

Type | Fields
--- | ---
NOT NULL | feature_cvtermprop_id
NOT NULL | feature_cvterm_id
FOREIGN KEY | feature_cvterm_id
NOT NULL | type_id
FOREIGN KEY | type_id
NOT NULL | rank
UNIQUE | feature_cvterm_id, type_id, rank

### feature_cvterm_dbxref

Top

Comments:
================================================
TABLE: feature_cvterm_dbxref
================================================
Additional dbxrefs for an association. Rows in the feature_cvterm table may be backed up by dbxrefs. For example, a feature_cvterm association that was inferred via a protein-protein interaction may be backed by by refering to the dbxref for the alternate protein. Corresponds to the WITH column in a GO gene association file (but can also be used for other analagous associations). See http://www.geneontology.org/doc/GO.annotation.shtml#file for more details.

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
feature_cvterm_dbxref_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
feature_cvterm_id | integer | 20 |  | UNIQUE, NOT NULL | feature_cvterm.feature_cvterm_id
dbxref_id | integer | 20 |  | UNIQUE, NOT NULL | dbxref.dbxref_id

### Indices

Name | Fields
--- | ---
feature_cvterm_dbxref_idx1 | feature_cvterm_id
feature_cvterm_dbxref_idx2 | dbxref_id

### Constraints

Type | Fields
--- | ---
NOT NULL | feature_cvterm_dbxref_id
NOT NULL | feature_cvterm_id
FOREIGN KEY | feature_cvterm_id
NOT NULL | dbxref_id
FOREIGN KEY | dbxref_id
UNIQUE | feature_cvterm_id, dbxref_id

### feature_cvterm_pub

Top

Comments:
================================================
TABLE: feature_cvterm_pub
================================================
Secondary pubs for an association. Each feature_cvterm association is supported by a single primary publication. Additional secondary pubs can be added using this linking table (in a GO gene association file, these corresponding to any IDs after the pipe symbol in the publications column.

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
feature_cvterm_pub_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
feature_cvterm_id | integer | 20 |  | UNIQUE, NOT NULL | feature_cvterm.feature_cvterm_id
pub_id | integer | 20 |  | UNIQUE, NOT NULL | pub.pub_id

### Indices

Name | Fields
--- | ---
feature_cvterm_pub_idx1 | feature_cvterm_id
feature_cvterm_pub_idx2 | pub_id

### Constraints

Type | Fields
--- | ---
NOT NULL | feature_cvterm_pub_id
NOT NULL | feature_cvterm_id
FOREIGN KEY | feature_cvterm_id
NOT NULL | pub_id
FOREIGN KEY | pub_id
UNIQUE | feature_cvterm_id, pub_id

### synonym

Top

Comments:
================================================
TABLE: synonym
================================================
A synonym for a feature. One feature can have multiple synonyms, and the same synonym can apply to multiple features.

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
synonym_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
name | varchar | 255 |  | UNIQUE, NOT NULL, The synonym itself. Should be human-readable machine-searchable ascii text. |
type_id | integer | 20 |  | UNIQUE, NOT NULL, Types would be symbol and fullname for now. | cvterm.cvterm_id
synonym_sgml | varchar | 255 |  | NOT NULL, The fully specified synonym, with any non-ascii characters encoded in SGML. |

### Indices

Name | Fields
--- | ---
synonym_idx1 | type_id

### Constraints

Type | Fields
--- | ---
NOT NULL | synonym_id
NOT NULL | name
NOT NULL | type_id
FOREIGN KEY | type_id
NOT NULL | synonym_sgml
UNIQUE | name, type_id

### feature_synonym

Top

Comments:
================================================
TABLE: feature_synonym
================================================
Linking table between feature and synonym.

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
feature_synonym_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
synonym_id | integer | 20 |  | UNIQUE, NOT NULL | synonym.synonym_id
feature_id | integer | 20 |  | UNIQUE, NOT NULL | feature.feature_id
pub_id | integer | 20 |  | UNIQUE, NOT NULL, The pub_id link is for relating the usage of a given synonym to the publication in which it was used. | pub.pub_id
is_current | boolean | 0 | false | NOT NULL, The is_current boolean indicates whether the linked synonym is the current -official- symbol for the linked feature. |

is_internal

boolean

0

false

NOT NULL, Typically a synonym exists so that somebody querying the db with an obsolete name can find the object theyre looking for (under its current name. If the synonym has been used publicly and deliberately (e.g. in a paper), it may also be listed in reports as a synonym. If the synonym was not used deliberately (e.g. there was a typo which went public), then the is_internal boolean may be set to -true- so that it is known that the synonym is -internal- and should be queryable but should not be listed in reports as a valid synonym.

### Indices

Name | Fields
--- | ---
feature_synonym_idx1 | synonym_id
feature_synonym_idx2 | feature_id
feature_synonym_idx3 | pub_id

### Constraints

Type | Fields
--- | ---
NOT NULL | feature_synonym_id
NOT NULL | synonym_id
FOREIGN KEY | synonym_id
NOT NULL | feature_id
FOREIGN KEY | feature_id
NOT NULL | pub_id
FOREIGN KEY | pub_id
NOT NULL | is_current
NOT NULL | is_internal
UNIQUE | synonym_id, feature_id, pub_id

### feature_contact

Top

Comments:
================================================
TABLE: feature_contact
================================================
Links contact(s) with a feature. Used to indicate a particular person or organization responsible for discovery or that can provide more information on a particular feature.

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
feature_contact_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
feature_id | integer | 20 |  | UNIQUE, NOT NULL | feature.feature_id
contact_id | integer | 20 |  | UNIQUE, NOT NULL | contact.contact_id

### Indices

Name | Fields
--- | ---
feature_contact_idx1 | feature_id
feature_contact_idx2 | contact_id

### Constraints

Type | Fields
--- | ---
NOT NULL | feature_contact_id
NOT NULL | feature_id
NOT NULL | contact_id
UNIQUE | feature_id, contact_id
FOREIGN KEY | contact_id
FOREIGN KEY | feature_id

Created by SQL::Translator 0.11020