Diagram
Table Descriptions
Database Model plugin_tmp.xml
ANA_ATTRIBUTION
This implements record level (but not column level) attribution for any record type that stores its ID in ANA_OBJECT. |
|
| ColumnName |
DataType |
PrimaryKey |
NotNull |
Flags |
Default Value |
Comment |
AutoInc |
| ATR_OID |
INTEGER |
PK |
NN |
UNSIGNED |
|
Persistent, unique, database wide ID of this attribution record. |
|
| ATR_OBJECT_FK |
INTEGER |
|
NN |
UNSIGNED |
|
Object this attribution is for. |
|
| ATR_SOURCE_FK |
INTEGER |
|
NN |
UNSIGNED |
|
Source for this attribution |
|
| ATR_EVIDENCE_FK |
VARCHAR(50) |
|
NN |
|
|
Type of evidence that occurred in source, in support of this attribution. |
|
| ATR_COMMENTS |
VARCHAR(255) |
|
|
|
|
Additional comments on this attribution. May be NULL, and usually is. |
|
|
|
| IndexName |
IndexType |
Columns |
| PRIMARY |
PRIMARY |
ATR_OID
|
| ATR_AK_INDEX |
Unique Index |
ATR_OBJECT_FK
ATR_SOURCE_FK
ATR_EVIDENCE_FK
|
| ANA_ATTRIBUTION_FKIndex1 |
Index |
ATR_OID
|
| ANA_ATTRIBUTION_FKIndex2 |
Index |
ATR_OBJECT_FK
|
| ANA_ATTRIBUTION_FKIndex3 |
Index |
ATR_SOURCE_FK
|
| ANA_ATTRIBUTION_FKIndex4 |
Index |
ATR_EVIDENCE_FK
|
|
ANA_DELETED_PUBLIC_ID
Keeps
a history of deleted public IDs so that we can direct queries that use
them to what they have been replaced with. This table is meant to keep
only a history of deleted PUBLIC IDs, not deleted internal IDs. |
|
| ColumnName |
DataType |
PrimaryKey |
NotNull |
Flags |
Default Value |
Comment |
AutoInc |
| DPI_DELETED_PUBLIC_ID |
VARCHAR(20) |
PK |
NN |
|
|
Public ID that has been deleted from the DB |
|
| DPI_EDITOR_FK |
INTEGER |
|
NN |
UNSIGNED |
|
Editor who deleted the ID |
|
| DPI_REASON_FK |
VARCHAR(20) |
|
NN |
|
|
Reason the ID was deleted. |
|
| DPI_DATETIME |
DATETIME |
|
NN |
|
|
When
the delete was done. This can be compared with the datetime on the
version to determine which version this delete happened in. |
|
| DPI_COMMENTS |
VARCHAR(255) |
|
|
|
|
Optional comments on why this was deleted. |
|
|
|
| IndexName |
IndexType |
Columns |
| PRIMARY |
PRIMARY |
DPI_DELETED_PUBLIC_ID
|
| ANA_DELETED_PUBLIC_ID_FKIndex1 |
Index |
DPI_REASON_FK
|
| ANA_DELETED_PUBLIC_ID_FKIndex2 |
Index |
DPI_EDITOR_FK
|
|
ANA_DELETE_REASON
There are a limited number of reasons why a public ID can be deleted. This table defines those reasons. |
|
| ColumnName |
DataType |
PrimaryKey |
NotNull |
Flags |
Default Value |
Comment |
AutoInc |
| DRE_NAME |
VARCHAR(20) |
PK |
NN |
|
|
Short text reason for delete. Can be diplayed. Examples are "merged", "split", "deleted" |
|
| DRE_COMMENTS |
VARCHAR(255) |
|
|
|
|
Description of reason. |
|
|
|
| IndexName |
IndexType |
Columns |
| PRIMARY |
PRIMARY |
DRE_NAME
|
|
ANAD_PART_OF
This table is derived
from ANA_NODE, ANA_TIMED_NODE, and ANA_RELATIONSHIP. It is used to
quickly display anatomy for a stage in an indented tree format. This
shows only part-of relationships. We display the anatomy as a tree,
even though it is actually a DAG. Displaying a DAG is too darn
difficult.
There can be multiple records in this table for
each corresponding record in ANA_NODE. There will be one record for
each possible path to any node.
Until we have some way of coping
with components under group nodes having the same component names, we
can't have a unique index on full path. |
|
| ColumnName |
DataType |
PrimaryKey |
NotNull |
Flags |
Default Value |
Comment |
AutoInc |
| APO_OID |
INTEGER |
PK |
NN |
UNSIGNED |
|
OID
for this record. Since this table is derived, this does not persist
across different generations of this table. This will always be equal
to APO_SEQUENCE. |
|
| APO_NODE_START_STAGE_FK |
INTEGER |
|
NN |
UNSIGNED |
|
Start stage of the node this record is for. This has the same value in all records for this node. |
|
| APO_NODE_END_STAGE_FK |
INTEGER |
|
NN |
UNSIGNED |
|
End stage of the node this record is for. This has the same value in all records for this node. |
|
| APO_PATH_START_STAGE_FK |
INTEGER |
|
NN |
UNSIGNED |
|
Start
stage of node as constrained by its place in the tree (that is, the
start stages of its ancestor nodes along the current path.) This can be
the same as or older than APO_NODE_START_STAGE_FK. This can have
different values in different records for this node. |
|
| APO_PATH_END_STAGE_FK |
INTEGER |
|
NN |
UNSIGNED |
|
End
stage of node as constrained by its place in the tree (that is, the end
stages of its ancestor nodes along the current path.) This can be the
same as or younger than APO_NODE_END_STAGE_FK. This can have different
values in different records for this node. |
|
| APO_SPECIES_FK |
VARCHAR(20) |
|
NN |
|
|
Species this record is for. |
|
| APO_NODE_FK |
INTEGER |
|
NN |
UNSIGNED |
|
Anatomy node this part of path record is for. |
|
| APO_SEQUENCE |
INTEGER |
|
NN |
UNSIGNED |
|
Absolute ordering of how this anatomy node relative to all other anatomy nodes. This starts at 0 and is dense. |
|
| APO_DEPTH |
INTEGER |
|
NN |
UNSIGNED |
|
How deep in the tree the item is / how much to indent the item. |
|
| APO_FULL_PATH |
VARCHAR(255) |
|
NN |
|
|
Full
path from root to this anatomy node. There can be multiple paths from
the root to any given node. Each path has its own record in this table.
The path consists of an ordered list of components, with each component
separated by a "." |
|
| APO_IS_PRIMARY |
BOOL |
|
NN |
|
|
True if this is the primary path to this node; false if it is a secondary (via group) path. |
|
| APO_IS_PRIMARY_PATH |
BOOL |
|
NN |
|
|
True
if this is the primary path to this node; false if the path to this
node contains a group node. PAY ATTENTION! Every node in the ontology,
including group nodes has exactly one primary path to it. If this
record shows that path, then this column will be true. If this record
shows a path that passes through a group to get to this node, the this
colum will be false. NOTE: This column is identical to and replaces
APO_IS_PRIMARY. APO_IS_PRIMARY will be dropped in Version 5 of the
database. |
|
| APO_PARENT_APO_FK |
INTEGER |
|
|
UNSIGNED |
|
OID of the ANAD_PART_OF record that is this record's parent. Is NULL for the root node. |
|
|
|
| IndexName |
IndexType |
Columns |
| PRIMARY |
PRIMARY |
APO_OID
|
| APOD_AK_INDEX |
Unique Index |
APO_SEQUENCE
|
| ANAD_PART_OF_FKIndex1 |
Index |
APO_NODE_START_STAGE_FK
|
| ANAD_PART_OF_FKIndex2 |
Index |
APO_NODE_END_STAGE_FK
|
| ANAD_PART_OF_FKIndex3 |
Index |
APO_NODE_FK
|
| ANAD_PART_OF_FKIndex5 |
Index |
APO_PATH_START_STAGE_FK
|
| ANAD_PART_OF_FKIndex6 |
Index |
APO_PATH_END_STAGE_FK
|
| ANAD_PART_OF_FKIndex6 |
Index |
APO_SPECIES_FK
|
| ANAD_PART_OF_FKIndex7 |
Index |
APO_PARENT_APO_FK
|
|
ANAD_PART_OF_PERSPECTIVE
A derived table supporting quick lookup of what ANAD_PART_OF records belong to each perspective. |
|
| ColumnName |
DataType |
PrimaryKey |
NotNull |
Flags |
Default Value |
Comment |
AutoInc |
| POP_PERSPECTIVE_FK |
VARCHAR(25) |
PK |
NN |
|
|
Perspective that APO record is in. |
|
| POP_APO_FK |
INTEGER |
PK |
NN |
UNSIGNED |
|
APO record that is a part of perspective. |
|
| POP_IS_ANCESTOR |
BOOL |
|
NN |
|
|
True
if this APO is not part of the perspective and is included here only to
give context for nodes that are in the perspective. If your application
wants to see only APOs that are contained in the perspective then
exclude ancestor records. |
|
| POP_NODE_FK |
INTEGER |
|
NN |
UNSIGNED |
|
Included here to speed up/simplify using this table. Note: This should be a foreign key pointing at APO_NODE_FK, but I couldn't get that to work in MySQL? |
|
|
|
| IndexName |
IndexType |
Columns |
| PRIMARY |
PRIMARY |
POP_PERSPECTIVE_FK
POP_APO_FK
|
| ANAD_PART_OF_PERSPECTIVE_FKIndex1 |
Index |
POP_APO_FK
|
| ANAD_PART_OF_PERSPECTIVE_FKIndex2 |
Index |
POP_PERSPECTIVE_FK
|
| ANAD_PART_OF_PERSPECTIVE_FKIndex3 |
Index |
POP_NODE_FK
|
|
ANAD_RELATIONSHIP_TRANSITIVE
This table is derived
from ANA_RELATIONSHIP. It contains the transitive closure of all
part-of relationships in that table. That is, for any nodes related in
a child-parent, or grandchild-grandparent, or
great-grandchild-great-grandparent, etc. relationships, there is an
entry in this table. Transitivity does not cross relationship types.
That is, the chain of relationships from ancestor to descdent will all
be of the same type.
This table also stores "self-refrential"
records. For example "brain" is a part of "brain". This greatly
simplifies the SQL to check for expression in a part and all its
descendents.
Eventually, it may also contain the transitive closure of lineage relationships as well. |
|
| ColumnName |
DataType |
PrimaryKey |
NotNull |
Flags |
Default Value |
Comment |
AutoInc |
| RTR_OID |
INTEGER |
PK |
NN |
UNSIGNED |
|
OID of this record. Since this table is derived this OID is not persistent across different derivations of this table |
AI |
| RTR_RELATIONSHIP_TYPE_FK |
VARCHAR(20) |
|
NN |
|
|
Type of relationship that connects every link in the chain from ancestor to descendent. |
|
| RTR_DESCENDENT_FK |
INTEGER |
|
NN |
UNSIGNED |
|
Descendent in relationship |
|
| RTR_ANCESTOR_FK |
INTEGER |
|
NN |
UNSIGNED |
|
Ancestor in relationship |
|
|
|
| IndexName |
IndexType |
Columns |
| PRIMARY |
PRIMARY |
RTR_OID
|
| RTR_AK_INDEX |
Unique Index |
RTR_ANCESTOR_FK
RTR_DESCENDENT_FK
RTR_RELATIONSHIP_TYPE_FK
|
| ANAD_RELATIONSHIP_TRANSITIVE_FKIndex1 |
Index |
RTR_RELATIONSHIP_TYPE_FK
|
| ANAD_RELATIONSHIP_TRANSITIVE_FKIndex2 |
Index |
RTR_ANCESTOR_FK
|
| ANAD_RELATIONSHIP_TRANSITIVE_FKIndex3 |
Index |
RTR_DESCENDENT_FK
|
|
ANA_EDITOR
Defines
editors in the database. Initially, the list of editors comes entirely
from the lineage annotation in the CIOF file. There are only 3 editors
to begin with. |
|
| ColumnName |
DataType |
PrimaryKey |
NotNull |
Flags |
Default Value |
Comment |
AutoInc |
| EDI_OID |
INTEGER |
PK |
NN |
UNSIGNED |
|
Persistent, unique database wide ID of the editor. |
|
| EDI_NAME |
VARCHAR(50) |
|
NN |
|
|
Name of editor |
|
|
|
| IndexName |
IndexType |
Columns |
| PRIMARY |
PRIMARY |
EDI_OID
|
| ANA_EDITOR_NAME_INDEX |
Unique Index |
EDI_NAME
|
| ANA_EDITOR_FKIndex1 |
Index |
EDI_OID
|
|
ANA_EVIDENCE
This is a qualifier on the type of evidence that exists in a source. Initially, there is only one value. |
|
| ColumnName |
DataType |
PrimaryKey |
NotNull |
Flags |
Default Value |
Comment |
AutoInc |
| EVI_NAME |
VARCHAR(50) |
PK |
NN |
|
|
Name of evidence type. |
|
|
|
| IndexName |
IndexType |
Columns |
| PRIMARY |
PRIMARY |
EVI_NAME
|
|
ANA_LOG
This table records updates to the database.
This
version of the table is database centric. The assumption is that in the
long run it will be populated by update and delete triggers on anatomy
tables. In the short term, it will be populated by the same scripts
that update the anatomy tables.
Since column names are unique in
the database, storing just the column name is sufficient. but it is
awkward. In order to find the table name the column is in, you need to
strip off the 3 letter column name prefix, and then look that up in
REF_ABBREVIATION. Not exactly graceful, but we can revisit when it
becomes a priority.
This table does not store the new column
value. Strictly speaking this is fine, since the new value is always
available in the database table. However, the table would probably be
easier to use if it stored both the old and new values.
The
original design for this table stored the datetime that the change
happened. |
|
| ColumnName |
DataType |
PrimaryKey |
NotNull |
Flags |
Default Value |
Comment |
AutoInc |
| LOG_OID |
INTEGER |
PK |
NN |
UNSIGNED |
|
Unique, persistent ID for this log entry. This is not unique across the database. |
AI |
| LOG_VERSION_FK |
INTEGER |
|
NN |
UNSIGNED |
|
|
|
| LOG_LOGGED_OID |
INTEGER |
|
NN |
UNSIGNED |
|
OID of record that was updated. This could be a foreign key to ANA_OBJECT, but then we couldn't keep history for deleted IDs. |
|
| LOG_COLUMN_NAME |
VARCHAR(64) |
|
NN |
|
|
Name of database column that was updated. Maximum 64 characters in a MySQL? column name. |
|
| LOG_OLD_VALUE |
VARCHAR(255) |
|
|
|
|
Old value of column expressed as a character string. |
|
| LOG_COMMENTS |
VARCHAR(255) |
|
|
|
|
Comments on this update. |
|
|
|
| IndexName |
IndexType |
Columns |
| PRIMARY |
PRIMARY |
LOG_OID
|
| ALOG_LOGGED_OID_INDEX |
Unique Index |
LOG_LOGGED_OID
LOG_COLUMN_NAME
|
| ANA_LOG_FKIndex1 |
Index |
LOG_VERSION_FK
|
|
ANA_NODE
This table implements the "abstract mouse". Defines every anatomy item in the organism.
Anatomy
is actually a directed acyclic graph (DAG), meaning there can be
multiple paths from any node in the graph back to the root.
Primary and group nodes are defined as :
- Primary
Component: These form the primary/canonical paths for navigating
anatomy. They define a tree structure, where there is a unique path
from the root to any component.
- Group Component: These were
introduced to allow alternative ways to navigate anatomy. They
effectively convert the anatomy tree to a DAG. Group compenents do not
participate in canonical paths, except at the end of the path.
It
was decided at the 05/04/2006 Anatomy database meeting, that while
canonical paths still exist they should be de-emphasised. See Mantis
issue 101.
:TODO: This ignores a key case: nested groups.
:TODO:
Validate that full_name is in fact the concatenation of the component
names of this node and all its primary, part-of, ancestor nodes.
|
|
| ColumnName |
DataType |
PrimaryKey |
NotNull |
Flags |
Default Value |
Comment |
AutoInc |
| ANO_OID |
INTEGER |
PK |
NN |
UNSIGNED |
|
Persistent, unique, database wide ID for this anatomy node |
|
| ANO_SPECIES_FK |
VARCHAR(20) |
|
NN |
|
|
Species this component occurs in. |
|
| ANO_COMPONENT_NAME |
VARCHAR(255) |
|
NN |
|
|
Name of this node. This is not unique. |
|
| ANO_IS_PRIMARY |
BOOL |
|
NN |
|
|
True if this node is a primary node, false if it is a group node. |
|
| ANO_IS_GROUP |
BOOL |
|
NN |
|
|
True
if the node is a group node, False if it is not. PAY ATTENTION: This
column is the inverse of and replaces ANO_IS_PRIMARY. ANO_IS_PRIMARY
will be dropped in version 5 of the database. The name/meaning of the
column was changed to make it more distinct from the
APO_IS_PRIMARY_PATH column in the ANAD_PART_OF table. |
|
| ANO_PUBLIC_ID |
VARCHAR(20) |
|
NN |
|
|
Published
ID of htis node. For Mouse, this is an EMAPA ID. I presume that EMAPA
was chosen so that we never accidentally display a node in the abstract
mouse. |
|
| ANO_DESCRIPTION |
VARCHAR(2000) |
|
|
|
|
Description of this component. Can be NULL. |
|
|
|
| IndexName |
IndexType |
Columns |
| PRIMARY |
PRIMARY |
ANO_OID
|
| anode_component_name_index |
Index |
ANO_COMPONENT_NAME
|
| anode_public_id_index |
Unique Index |
ANO_PUBLIC_ID
|
| ANA_NODE_FKIndex2 |
Index |
ANO_OID
|
| ANA_NODE_FKIndex2 |
Index |
ANO_SPECIES_FK
|
|
ANA_NODE_IN_PERSPECTIVE
THIS TABLE IS DEPRECATED. IT HAS BEEN REPLACED BY ANA_PERSPECTIVE_AMBIT. THIS TABLE WILL BE DROPPED IN VERSION 5.
Defines what nodes participate in what perspectives.
Initially,
perspective membership lists are minimal. You pick the nodes from the
anatomy you want to be in the perspective. All parent and child nodes
of the selected nodes are then assumed to also be in the perspective.
As long as this model works, the minimal membership lists will work
well. |
|
| ColumnName |
DataType |
PrimaryKey |
NotNull |
Flags |
Default Value |
Comment |
AutoInc |
| NIP_OID |
INTEGER |
PK |
NN |
UNSIGNED |
|
Unique ID of this row. This is not unique across the database. |
AI |
| NIP_PERSPECTIVE_FK |
VARCHAR(25) |
|
NN |
|
|
Perspective the node is a part of. |
|
| NIP_NODE_FK |
INTEGER |
|
NN |
UNSIGNED |
|
Node that is a part of the perspective. |
|
|
|
| IndexName |
IndexType |
Columns |
| PRIMARY |
PRIMARY |
NIP_OID
|
| ANA_NODE_IN_PERSPECTIVE_FKIndex1 |
Index |
NIP_NODE_FK
|
| ANA_NODE_IN_PERSPECTIVE_FKIndex2 |
Index |
NIP_PERSPECTIVE_FK
|
|
ANA_OBJECT
This table is a central place to store all persistent internal IDs. This table exists for two reasons:
- To store attributes, such as creation time, that are common to all objects.
- So that we can have generic tables that still support foreign keys.
|
|
| ColumnName |
DataType |
PrimaryKey |
NotNull |
Flags |
Default Value |
Comment |
AutoInc |
| OBJ_OID |
INTEGER |
PK |
NN |
UNSIGNED |
|
ID value |
|
| OBJ_CREATION_DATETIME |
DATETIME |
|
|
|
|
Date and time when this ID and its corresponding record were created. |
|
| OBJ_CREATOR_FK |
INTEGER |
|
|
UNSIGNED |
|
ID
of editor who created this object. This is usually NULL, as we usually
don't know. This field should be an FK to ANA_EDITOR, but MySQL? (or
maybe just DBDesigner) does not support circular references. |
|
|
|
| IndexName |
IndexType |
Columns |
| PRIMARY |
PRIMARY |
OBJ_OID
|
|
ANA_PERSPECTIVE
Perspectives
are particular views of subsets of the anatomy. Initially they are used
to display only parts of the anatomy graph instead of the whole darn
thing.
|
|
| ColumnName |
DataType |
PrimaryKey |
NotNull |
Flags |
Default Value |
Comment |
AutoInc |
| PSP_NAME |
VARCHAR(25) |
PK |
NN |
|
|
Name of perspective. |
|
| PSP_COMMENTS |
VARCHAR(1024) |
|
|
|
|
Description of perspective |
|
|
|
| IndexName |
IndexType |
Columns |
| PRIMARY |
PRIMARY |
PSP_NAME
|
|
ANA_PERSPECTIVE_AMBIT
Defines
the nodes that form the boundary of the perspective. The boundary is
the minimal defintion of what nodes are in a perspective. Nodes in the
boundary are part of the perspective.
Boundary nodes are defined as START nodes, STOP nodes, or both:
- START:
Define the top of the perspective. The perspective includes START nodes
plus their descendents. Every perspective must have at least one START
node
- STOP: Define the bottom (or lower limit) of the
perspecitve. This says "include this node in the perspective, but none
of its children." STOP nodes are optional. If they are present then
they must be descendants of al least one of the START nodes.
- START
and STOP: It is possible for a node to be both a START and a STOP node.
This says "include this node, but none of its children. This is useful
for creating a flat list of terms.
START nodes tend to
be towards the top of the anatomy and therefore tend to be few in
number. STOP nodes, if present tend to be lower in the anatomy and can
therefore be much more numerous. If a perspective contains only START
nodes then every descendant of every start node is included in the
perspective.
ANAD_PART_OF_PERSPECTIVE.POP_IS_ANCESTOR for how ancestor nodes of START nodes can be used with perspectives, if you want to.
- Ancestor nodes are not part of the perspective.
- However,
include the ancestor nodes along the primary path (meaning no group
paths) to all START nodes in the derived ANAD_PART_OF_PERSPECTIVE
table. However, flag them as non-member primary path ancestor nodes
- It
is then up to the application if it wants to display the ancestor nodes
or not, and if so, if it wants to treat them differently.
|
|
| ColumnName |
DataType |
PrimaryKey |
NotNull |
Flags |
Default Value |
Comment |
AutoInc |
| PAM_OID |
INTEGER |
PK |
NN |
UNSIGNED |
|
Unique OID for this record. This is persistent across database versions and is unique across the database. |
|
| PAM_PERSPECTIVE_FK |
VARCHAR(25) |
|
NN |
|
|
Name of perspective that this node defines a border point for. |
|
| PAM_NODE_FK |
INTEGER |
|
NN |
UNSIGNED |
|
OID
of node that forms part of the perspective's ambit. That is, this node
is part of this perspective's border. Nodes in the border are part of
the perspective. |
|
| PAM_IS_START |
BOOL |
|
NN |
|
|
True: This node forms part (or all) of the upper border of the perspective.. |
|
| PAM_IS_STOP |
BOOL |
|
NN |
|
|
True: This node forms part of the lower border of the perspective. |
|
| PAM_COMMENTS |
VARCHAR(255) |
|
|
|
|
Why this node is a start and/or stop node in this perspective. |
|
|
|
| IndexName |
IndexType |
Columns |
| PRIMARY |
PRIMARY |
PAM_OID
|
| ANA_PERSPECTIVE_AMBIT_FKIndex1 |
Index |
PAM_OID
|
| ANA_PERSPECTIVE_AMBIT_FKIndex2 |
Index |
PAM_PERSPECTIVE_FK
|
| ANA_PERSPECTIVE_AMBIT_FKIndex3 |
Index |
PAM_NODE_FK
|
| PAM_AK_INDEX |
Index |
PAM_PERSPECTIVE_FK
PAM_NODE_FK
|
|
ANA_RELATIONSHIP
Defines
binary, ordered relationships between objects in a mouse database.
Well, only certain types of binary, ordered relationships.
Initially
(04/2006), this table only contains relationships between nodes, and
relationships between timed nodes. More broadly, in initially contains
binary ordered relationships between objects of the same type.
We
also thought about redefining timed nodes as a relationship between a
node and a stage and then storing that relationship in this table. In
that case, relationships between timed nodes would become relationships
between relationships. While this has a certain elegance, if you take
it to its extreme conclusion then you end up with a two table database:
objects, and relationships. I don't want to go there.
|
|
| ColumnName |
DataType |
PrimaryKey |
NotNull |
Flags |
Default Value |
Comment |
AutoInc |
| REL_OID |
INTEGER |
PK |
NN |
UNSIGNED |
|
Persistent, unique, database wide ID for this relationship |
|
| REL_RELATIONSHIP_TYPE_FK |
VARCHAR(20) |
|
NN |
|
|
Type of relationship |
|
| REL_CHILD_FK |
INTEGER |
|
NN |
UNSIGNED |
|
OID of Child in relatonship |
|
| REL_PARENT_FK |
INTEGER |
|
NN |
UNSIGNED |
|
OID of parent in relationship |
|
| REL_SEQUENCE |
INTEGER |
|
|
UNSIGNED |
|
Order
to display the child item underneath the parent item, relative to the
parent's other children. Ties are broken alphabetically. If this is
NULL, then order is up to application, but general rule is display
NULLs in alphabetical order, after all items with a specified order. |
|
|
|
| IndexName |
IndexType |
Columns |
| PRIMARY |
PRIMARY |
REL_OID
|
| REL_AK_IINDEX |
Index |
REL_PARENT_FK
REL_CHILD_FK
REL_RELATIONSHIP_TYPE_FK
|
| ANA_RELATIONSHIP_FKIndex1 |
Index |
REL_RELATIONSHIP_TYPE_FK
|
| ANA_RELATIONSHIP_FKIndex2 |
Index |
REL_PARENT_FK
|
| ANA_RELATIONSHIP_FKIndex3 |
Index |
REL_CHILD_FK
|
| ANA_RELATIONSHIP_FKIndex4 |
Index |
REL_OID
|
|
ANA_RELATIONSHIP_TYPE
Defines
relationship types. All types are assumed to be directional from
"child" to "parent", and are assumed to be transitive. This contradicts
DAG-Edit which supports a wide variety of relationships including,
cyclic, non-transitive, and non-directional relationships. The use of
"parent" and "child" differs from GMOD which uses "subject" and
"object" in its relationships. In GMOD subject means child and object
means parent. As long as we only have directional relationships, I
favour parent and child because it is clearer.
Relationship names use the child to parent form of the relationship.
There
is no "is-a" relationship in a mouse atlas database. The semantics of
"is-a" in the world of ontologies is that any property that a parent
has is also held by the children. Thus, saying that a gene is expressed
in a parent node of an is-a relationship, would imply that the gene is
expressed in all the children. This would rarely be the case. However,
is-a could have a place in terms of describing anatomy. Properties held
by "muscle" would also be help by "slow muscle", "fast muscle", and
"smooth muscle". It just doesn't work for gene expression.
"develops-from" relationships are complicated in the mouse atlas. They have (at least) 3 related meanings:
- Same
path, same compenent, next stage: For example, "body/brain in stage x
devolops into body/brain in stage x+1". This meaning is necessary
because of Mouse Atlas's use of timed nodes.
- Differnet
path, same component, next stage: For example "body/brain in stage x
devolops into body/head/brain in stage x+1". In this case, the
component (brain) is the same, but the path to get to it has changed.
This is necessary because of Mouse Atlas's use of full paths to
uniquely identify parts.
- Different path, different
component, next stage: For Example, ".../metanephros/comma shaped
nephron in stage x devolops into .../metanephros/s-shaped nephron in
stage x+1". In this case the component changes.
I
believe that the "Different path, spawned component, same or next
stage" case, where only part of the original component develops into a
different part (think mesenchyme) is a special case of the last item. |
|
| ColumnName |
DataType |
PrimaryKey |
NotNull |
Flags |
Default Value |
Comment |
AutoInc |
| RTY_NAME |
VARCHAR(20) |
PK |
NN |
|
|
Name of relationship, such as "part-of" "develops-from", etc. Relationship names reflect the child to parent relationship. |
|
| RTY_CHILD_TO_PARENT_DISPLAY |
VARCHAR(40) |
|
NN |
|
|
Use when displaying the relationship from the child's point of view, e.g., "part of", "develops from" |
|
| RTY_PARENT_TO_CHILD_DISPLAY |
VARCHAR(40) |
|
NN |
|
|
Use this when displaying the relationship from the parent's point of view. e.g., "has part", "develops into" |
|
|
|
| IndexName |
IndexType |
Columns |
| PRIMARY |
PRIMARY |
RTY_NAME
|
|
ANA_REPLACED_BY
Tables
lists what public ID(s) replaced a deleted public ID. We keep track of
this information so that we can redirect users who are using the old
IDs. |
|
| ColumnName |
DataType |
PrimaryKey |
NotNull |
Flags |
Default Value |
Comment |
AutoInc |
| RPL_NEW_PUBLIC_ID |
VARCHAR(20) |
PK |
NN |
|
|
(One of) the public ID(s) that replaced it, if it was replaced. |
|
| RPL_OLD_PUBLIC_ID |
VARCHAR(20) |
PK |
NN |
|
|
Public ID that was replaced. |
|
|
|
| IndexName |
IndexType |
Columns |
| PRIMARY |
PRIMARY |
RPL_NEW_PUBLIC_ID
RPL_OLD_PUBLIC_ID
|
| ANA_REPLACED_BY_FKIndex1 |
Index |
RPL_OLD_PUBLIC_ID
|
|
ANA_SOURCE
Deescribes sources of information. These can be books, journal article, personal communication, .... |
|
| ColumnName |
DataType |
PrimaryKey |
NotNull |
Flags |
Default Value |
Comment |
AutoInc |
| SRC_OID |
INTEGER |
PK |
NN |
UNSIGNED |
|
Persistent, unique, database-wide ID for this source. |
|
| SRC_NAME |
VARCHAR(255) |
|
NN |
|
|
Name of source. Could be book name, or Article name |
|
| SRC_AUTHORS |
VARCHAR(255) |
|
NN |
|
|
List of authors in order and as they occur in the source. |
|
| SRC_FORMAT_FK |
VARCHAR(30) |
|
NN |
|
|
Format the source is in. |
|
| SRC_YEAR |
YEAR |
|
|
|
|
Year source was created/published. We could store a full date here, but most of the time that is not appropriate. |
|
|
|
| IndexName |
IndexType |
Columns |
| PRIMARY |
PRIMARY |
SRC_OID
|
| ANA_SOURCE_AK_INDEX |
Unique Index |
SRC_NAME
SRC_AUTHORS
|
| ANA_SOURCE_FKIndex1 |
Index |
SRC_OID
|
| ANA_SOURCE_FKIndex2 |
Index |
SRC_FORMAT_FK
|
|
ANA_SOURCE_FORMAT
I thought about other names for the table: source_type, source_medium. Still looking for a better name. |
|
| ColumnName |
DataType |
PrimaryKey |
NotNull |
Flags |
Default Value |
Comment |
AutoInc |
| SFM_NAME |
VARCHAR(30) |
PK |
NN |
|
|
Type of source, e.g. "book", "journal article", "personal communication" |
|
|
|
| IndexName |
IndexType |
Columns |
| PRIMARY |
PRIMARY |
SFM_NAME
|
|
ANA_STAGE
Defines
the standard staging series for a species. There is only one standard
staging series per species and all alternative series are mapped to it.
For mouse the one true staging series is Theiler. For Xenopus it is
N&F.
|
|
| ColumnName |
DataType |
PrimaryKey |
NotNull |
Flags |
Default Value |
Comment |
AutoInc |
| STG_OID |
INTEGER |
PK |
NN |
UNSIGNED |
|
Persistent, unique, database wide ID for this stage. |
|
| STG_SPECIES_FK |
VARCHAR(20) |
|
NN |
|
|
Species this stage is for. |
|
| STG_NAME |
VARCHAR(20) |
|
NN |
|
|
Name of the stage, e.g. TS01 |
|
| STG_SEQUENCE |
INTEGER |
|
NN |
UNSIGNED |
|
Definitive ordering of this stage relative to all other stages for this species. |
|
| STG_DESCRIPTION |
VARCHAR(2000) |
|
|
|
|
Alternatively, could have a URL here. |
|
| STG_SHORT_EXTRA_TEXT |
VARCHAR(25) |
|
NN |
|
|
Very
short additional text describing the stage. This is useful when real
estate is tight but you still have enough space to give the user some
additional information besides just the somtimes uninformative stage
name. For mouse, this will likely be an equivalent DPC stage range. For
Xenopus, this will likely be the short term for the stage, e.g.,
blastula. |
|
| STG_PUBLIC_ID |
VARCHAR(20) |
|
|
|
|
Public ID of stage. NULL means that staging series does not have public IDs. |
|
|
|
| IndexName |
IndexType |
Columns |
| PRIMARY |
PRIMARY |
STG_OID
|
| STG_NAME_INDEX |
Unique Index |
STG_NAME
|
| STG_SEQUENCE_INDEX |
Unique Index |
STG_SEQUENCE
|
| ANA_STAGE_FKIndex2 |
Index |
STG_OID
|
| ANA_STAGE_FKIndex2 |
Index |
STG_SPECIES_FK
|
|
ANA_STAGE_MODIFIER
Sometimes
a part appears particularly early or disappears particularly late in a
stage. In such cases it is helpful to specify this in the anatomy.
We
could further subdivide modifiers into those that can be applied to
start stages, and those that can be applied to end stages. Might do
this if it becomes a problem.
:TODO: Currently
(03/2006) all stage modifiers are applied to either the first or last
stage that a node exists at. Verify that stage modifiers exist only at
start and stop stages.
|
|
| ColumnName |
DataType |
PrimaryKey |
NotNull |
Flags |
Default Value |
Comment |
AutoInc |
| SMO_NAME |
VARCHAR(20) |
PK |
NN |
|
|
Modifier name: "Appears early", "Appears late", "Lost early", "Lost late" |
|
|
|
| IndexName |
IndexType |
Columns |
| PRIMARY |
PRIMARY |
SMO_NAME
|
|
ANA_SYNONYM
This table support synonyms for any anatomy object that has its ID in ANA_OBJECT. |
|
| ColumnName |
DataType |
PrimaryKey |
NotNull |
Flags |
Default Value |
Comment |
AutoInc |
| SYN_OID |
INTEGER |
PK |
NN |
UNSIGNED |
|
Persistent, unique database wide ID of this synonym |
|
| SYN_OBJECT_FK |
INTEGER |
|
NN |
UNSIGNED |
|
Object this is a synonym for. |
|
| SYN_SYNONYM |
VARCHAR(100) |
|
NN |
|
|
synonym |
|
|
|
| IndexName |
IndexType |
Columns |
| PRIMARY |
PRIMARY |
SYN_OID
|
| SYN_SYNONYM_INDEX |
Index |
SYN_SYNONYM
|
| SYN_AK_INDEX |
Unique Index |
SYN_OBJECT_FK
SYN_SYNONYM
|
| ANA_SYNONYM_FKIndex1 |
Index |
SYN_OID
|
| ANA_SYNONYM_FKIndex2 |
Index |
SYN_OBJECT_FK
|
|
ANA_TIMED_NODE
Contains
a record for each individual stage that an anatomy node exists in. This
table, more than any other table, defines what EMAP looks like to the
outside world.
The GUDMAP and EuReGene? projects use timed nodes
to record annotation, but their query interface is much more driven by
the abstract mouse defined in ANA_NODE.
|
|
| ColumnName |
DataType |
PrimaryKey |
NotNull |
Flags |
Default Value |
Comment |
AutoInc |
| ATN_OID |
INTEGER |
PK |
NN |
UNSIGNED |
|
Persistent, unique, database wide ID of this timed node |
|
| ATN_NODE_FK |
INTEGER |
|
NN |
UNSIGNED |
|
Untimed node this timed node is for. |
|
| ATN_STAGE_FK |
INTEGER |
|
NN |
UNSIGNED |
|
Particualr stage at which this timed node exists. |
|
| ATN_STAGE_MODIFIER_FK |
VARCHAR(20) |
|
|
|
|
Some timed nodes have stage modifiers, such as late or early. NULL for most records, meaning no modifier. |
|
| ATN_PUBLIC_ID |
VARCHAR(20) |
|
NN |
|
|
This is the public ID of this node. This, once generated, never changes. For EMAP, this takes the form "EMAP:nnn" |
|
|
|
| IndexName |
IndexType |
Columns |
| PRIMARY |
PRIMARY |
ATN_OID
|
| atn_public_id_index |
Unique Index |
ATN_PUBLIC_ID
|
| ATN_AK2_INDEX |
Unique Index |
ATN_NODE_FK
ATN_STAGE_FK
|
| ANA_TIMED_NODE_FKIndex1 |
Index |
ATN_STAGE_FK
|
| ANA_TIMED_NODE_FKIndex2 |
Index |
ATN_STAGE_MODIFIER_FK
|
| ANA_TIMED_NODE_FKIndex3 |
Index |
ATN_OID
|
| ANA_TIMED_NODE_FKIndex4 |
Index |
ATN_NODE_FK
|
|
ANA_VERSION
Stores
version number of the database as a whole. Updates to the anatomy
database are done infrequently, and when done, they are done as a
coherent whole. This table records those updates. Each such update is a
new version.
|
|
| ColumnName |
DataType |
PrimaryKey |
NotNull |
Flags |
Default Value |
Comment |
AutoInc |
| VER_OID |
INTEGER |
PK |
NN |
UNSIGNED |
|
Unique, persistent database wide OID for this version. |
|
| VER_NUMBER |
INTEGER |
|
NN |
UNSIGNED |
|
Version
number of the anatomy database as a whole. Assumes integer version
numbers. This could easily be a string supporting sub-version numbers. |
|
| VER_DATE |
DATETIME |
|
NN |
|
|
Datetime version was released. |
|
| VER_COMMENTS |
VARCHAR(2000) |
|
NN |
|
|
Comments on this version. |
|
|
|
| IndexName |
IndexType |
Columns |
| PRIMARY |
PRIMARY |
VER_OID
|
| AVERS_NUMBER_INDEX |
Unique Index |
VER_NUMBER
|
| AVERS_DATE |
Unique Index |
VER_DATE
|
| ANA_VERSION_FKIndex1 |
Index |
VER_OID
|
|
REF_SPECIES
|
|
| ColumnName |
DataType |
PrimaryKey |
NotNull |
Flags |
Default Value |
Comment |
AutoInc |
| RSP_NAME |
VARCHAR(20) |
PK |
NN |
|
|
Name that is most often displayed for the species, e.g. mouse, Xenopus laevis, etc. |
|
| RSP_LATIN_NAME |
VARCHAR(30) |
|
NN |
|
|
Latin name of the species, e.g. mus musculus, Xenopus laevis, etc |
|
| RSP_TIMED_NODE_ID_PREFIX |
VARCHAR(20) |
|
NN |
|
|
For
species, that have defined ontologies, this is the prefix to in front
of IDs for timed nodes from that ontology. For example, "EMAP:". |
|
| RSP_NODE_ID_PREFIX |
VARCHAR(20) |
|
NN |
|
|
For
species that have defined ontologies, this the prefix in front of the
IDs for abstract nodes in that ontology. For exaple, "EMAPA:" |
|
|
|
| IndexName |
IndexType |
Columns |
| PRIMARY |
PRIMARY |
RSP_NAME
|
| species_latin_name_index |
Unique Index |
RSP_LATIN_NAME
|
|