Copy_of_AnimalDatabasev4.xls
XLS spreadsheet 2 MB Posted
- Attached to
- A-GRIN Webpage Redesign
- Solicitation number
- AG-8KAP-S-17-0002
About this file
Q: Do you have the ability to export the database schema? The site looks pretty simple but having the database schema would make it way more obvious what this tool is doing and how complex it is. (not necessarily the data, just the tables/columns) A: I have attached a data dictionary in .xls format. This is slightly out of date but is generally accurate.
View the file
Other files
| File | Type | Posted |
|---|---|---|
| Additional_Clarification_from_Current_Database_Maintainer.pdf | ||
| FBO_Q&As1.pdf |
On GovTribe
Work with this file
- Download the original file
- Contacts named in this file
- Similar government files
- Ask GovTribe AI about this file
Text version
TABLE OF CONTENTS
| ANIMAL DATABASE DATA DICTIONARY | ||
| Author: | Lark Wessel | |
| Created: | 01.09.2008 | |
| Last Revised Date: | Ted Manahan 5/8/2015 | |
| DIRECTIONS | -You may click on any of the hyperlinks below in order to go directly to that tab. |
-You may click on the "Return to Table of Contents" link at the bottom of each table detail tab to return to this page.
SCENARIO NAGP receives/collects animal material (blood, semen, etc.) from various entities for storage, research, etc. The samples provided that are already cryopreserved are inventoried directly into the repository. Fresh shipments are processed in the lab according to the appropriate protocol and are then inventoried into the repository.
Individual animal data is then recorded into the Animal GRIN database. Data about each animal is obtained from the sample provider, breed associations, various web sites, and other sources. This data includes taxonomy (genus, species, breed, etc.), identification, breeder, observations, and pedigree information. Viability data is collected by NAGP and entered into the database. Location records of the inventoried samples are also recorded in GRIN.
Germplasm requests are handled via the request area. Individual animals and specific location records are selected, inventory is pulled from the repository, and the database removes the inventory, allowing an accurate count of inventory for the repository.
COMMENTS Every table has a DEFINITION and DELETION RULE defined for it.
Every foreign key field has one of the following designations in the Notes column:
1) NO ACTION - indicating that the record should not be deleted and the referenced record can not be deleted.
2) ON DELETE CASCADE - indicating that if the referenced record is deleted then all related records should also be deleted.
3) SET TO NULL - indicating that if the referenced record is deleted, then the foreign key field should be blanked out.
In addition, there are some other notes written in all CAPS for the other constraints. (APP) has been added after constraints which must take place through the application rather than in the database scripts, the font is also red on those.
The tables are specified as records which are shared across all countries or records which are not shared.
| ASSUMPTIONS | **none defined at this time.** |
| TABLE DETAIL KEY | PK Primary Key |
FK Foreign Key (Bold) Required Fields
| TITLE / SUMMARY TABS | ||
| TABLE OF CONTENTS | Displays the contents of this file. | |
| DATABASE MODEL | Displays the relational database diagram. | |
| DATABASE MODEL - DATABASE TABLE | Displays the database tables diagram. | |
| TERMS | Displays a list of terms and their definitions. | |
| INCOMING ORDER INFORMATION TABS | ||
| INCOMING_ORDERS TABLE | This table stores the information about incoming orders of materials received. | |
| INCOMING_ORDER_ANIMALS TABLE | This table provides a many-to-many link between the INCOMING_ORDERS and the ANIMALS tables. | |
| ENTITIES TABLE | This table stores the information about people and organizations. | |
| ADDRESSES TABLE | This table stores all address information associated with an ENTITY. | |
| ENT_OBSERVATIONS TABLE | This table stores observations associated with an entity. This will usually be with regard to the environment. | |
| AGREEMENTS TABLE | This table stores the agreement information for all agreements made between us and an entity which is providing inventory. Not all will have agreements. | |
| INVENTORY INFORMATION TABS | ||
| INVENTORIES TABLE | This table stores the inventory information. This is essentially the line item information from the INCOMING_ORDERS. This also stores the allocation distribution of the inventory among the allocation categories: Core, Evaluation, Restricted, and Working. | |
| LOCATIONS TABLE | This table stores one level of detail for a specific location. For example, the pie information for a specific set of locations. | |
| INV_OBSERVATIONS TABLE | This table stores observations made about a specific inventory from an animal. | |
| ALLOCATION_RULES TABLE | This table allows rules to be set up regarding the allocation based on the entity, animal, or taxonomy. This will be used to create the default allocation record which can then be accepted or overridden by the user when entering the inventory into the database. | |
| ALLOCATION_RULE_INVENTORIES TABLE | This table provides a way to track which rule was used for the allocation of a specific inventory and when. | |
| ANIMAL INFORMATION TABS | ||
| ANIMALS TABLE | This table stores information about an animal from who we have inventory as well as animals who are entries in the pedigree (family tree) of animals from who we have inventory. | |
| ANM_OBSERVATIONS TABLE | This table stores observations about a specific animal. | |
| IDENTIFICATIONS TABLE | This table stores all identification for an animal. For example, the registration number, name, tattoo, etc. | |
| COEFFICIENT_RELATIONSHIPS TABLE | This table stores each set of related animals and their relationship coefficient. For example, if it is the sire and the child in this entry the coefficient would be 0.5. | |
| TAXONOMY STRUCTURE TABS | ||
| TAXONOMIES TABLE | This table stores all taxonomy records for all levels of the taxonomy structure. | |
| TAX_COMM_NAMES TABLE | This table stores all of the common names for a taxonomy entry. | |
| TAXONOMY_PURPOSES TABLE | This table stores all taxonomy purpose records. | |
| TAX_OBSERVATIONS TABLE | This table stores the observations made about specific taxonomy. This can contain observations about any taxonomy at any level of the taxonomy structure. | |
| DESCRIPTOR TABS | ||
| DESCRIPTORS TABLE | This table holds the framework for each type of descriptor which has been entered into the database. | |
| DESCRIPTOR_CODES TABLE | This table stores the possible values for the different descriptors. | |
| DESCRIPTOR_TAXONOMIES TABLE | This table provides a many to many link between the Descriptor table and the taxonomy table as each descriptor may be attached to more than one taxonomy and each taxonomy may have more than one descriptor. | |
| PROTOCOL TABS | ||
| PROTOCOLS TABLE | This table stores the protocols used on inventory in the repository. | |
| PROTOCOL_INVS TABLE | This table provides a many to many link between the Protocol table and the Inventory table as each inventory could have more than one protocol used on it, or the same used on it more than once. | |
| PROTOCOL_RECIPES TABLE | This table provides a many to many link between the Protocol table and the Recipe table as each protocol may have more than one recipe and each recipe may be associated with more than one protocol. | |
| PROTOCOL_TAXES TABLE | This table provides a many to many link between the Protocol table and the Taxonomy table as each protocol may apply to more than one taxonomy entry and each taxonomy entry may have more than one protocol associated with it. | |
| RECIPES TABLE | This table stores recipe information. The recipe is the formula used in a protocol. | |
| REQUEST TABS | ||
| REQUEST_HEADERS TABLE | This table stores the request header information for when inventory is requested from a repository. | |
| REQUEST_LINES TABLE | This table stores the request line item information for when inventory is requested from a repository. | |
| REQUEST_LINE_LOCS TABLE | This table provides a many to many link between the Request Lines table and the Location table as each line (inventory) requested in a request may be made up of the contents of many locations. | |
| REQUEST_RESULTS TABLE | This table stores the results from a request. The record may be attached to the request header, if it applies to all lines, or to a specific request line if it is applicable to only that animal. | |
| GENOMICS TABS | ||
| SNP_FILE_FORMATS TABLE | This table stores the name of a SNP chip used for genomic data upload. | |
| SNP_FILE_COLUMNS TABLE | This table stores the name of SNPs used for genomic data upload. | |
| SNP_DBSNP_REFERENCE_NUMBER TABLE | This table stores the sbSNP name of SNPs used for genomic data upload. | |
| SNP_FILE_UPLOADS TABLE | This table stores the information on each genomic data file uploaded into the database. | |
| SNP_FILE_ANIMLALS TABLE | This table stores information on a genotyping for an individual animal. | |
| SNP_DATA_REQUESTS TABLE | This table stores a record of each user request for a download of genomic data. | |
| MISCELLANEOUS TABS | ||
| IMAGES TABLE | This table stores images associated with a specific taxonomy entry and/or a specific animal. | |
| DELAYED_JOBS TABLE | This table stores information required for background processing of long-running jobs. | |
| REFERENCE TABS | ||
| COUNTRY_REFS TABLE | This table stores the official two letter country abbreviation and name as provided by http://www.iso.org/iso/list-en1-semic-2.txt | |
| COUNTRY_SUBDIVISION_REFS TABLE | This table stores the subdivision name and the official two letter abbreviation as well as to which country it belongs as provided by ????? | |
| TAX_CROSS_REFS TABLE | This table stores the cross reference for taxonomy records in order to enable taxonomies from different countries to be linked as equitable. | |
| TAX_RELATIVES TABLE | This table keeps a list of each taxonomy and all of the other taxonomy records to which it is related. This was developed primarily for assistance with the drilldown web pages. | |
| DESCRIPTOR_CROSS_REFS TABLE | This table keeps a cross reference list of the ANM descriptors and the TAX descriptor which it maps to. This was initially developed for assistance with the Compare Collection to Taxonomy report. | |
| LOCATION_STRUCTURE_REFS | This table stores the location structures for specified container types including which levels are required and which are optional. | |
| INFO_SOURCE_REFS | This table stores the drop down menu options for the information source fields throughout the database. | |
| TAX_PURPOSE_REFS | This table stores the drop down menu options for the taxonomy purpose field. | |
| BIRTH_PLACE_REFS | This table stores the drop down menu options for the animal birth place field. | |
| UNIT_FORM_REFS | This table stores the drop down menu options for the inventory unit form field. | |
| UNIT_SIZE_REFS | This table stores the drop down menu options for the inventory unit size field. | |
| PROFESSION_REFS | This table stores the drop down menu options for the entity profession field. | |
| DATABASE TABLES | ||
| DB_USERS TABLE | This table stores the user log on information for the database. | |
| DB_USER_GROUPS TABLE | This table stores the user groups to which a user may belong. It defines what rights each group has. | |
| DB_USER_GRP_RIGHTS TABLE | This table serves as the many-to-many table detailing which user groups have which user rights. | |
| DB_USER_RIGHTS TABLE | This table stores the possible rights which a user group may be granted. | |
| DB_OBJECTS TABLE | This table stores the objects about the database including fields, tables, views, etc. | |
| DB_CODES TABLE | This table stores the codes for the coded fields and links them to the appropriate DB_OBJECTS. | |
| AGR_HISTORIES TABLE | This table stores the history of every agreement entered in the AGREEMENTS table. | |
| AGR_NOTIFICATIONS TABLE | This table stores the notification of expiration history associated with the AGREEMENTS records. |
&LPg. &P of &N&C&F&R&D &T
TERMS
INCOMING_ORDERS TABLE
AGREEMENTS TABLE
INVENTORIES TABLE
LOCATIONS TABLE
INV_OBSERVATIONS TABLE
ANIMALS TABLE
ANM_OBSERVATIONS TABLE
TAXONOMIES TABLE
TAX_OBSERVATIONS TABLE
DESCRIPTORS TABLE
DESCRIPTOR_TAXONOMIES TABLE
PROTOCOLS TABLE
RECIPES TABLE
REQUEST_HEADERS TABLE
ENT_OBSERVATIONS TABLE
REQUEST_LINES TABLE
DELAYED_JOBS TABLE
IDENTIFICATIONS TABLE
COEFFICIENT_RELATIONSHIPS TABLE
PROTOCOL_RECIPES TABLE
PROTOCOL_TAXES TABLE
REQUEST_RESULTS TABLE
TAXONOMY_PURPOSES TABLE
ALLOCATION_RULE_INVENTORIES TABLE
TAX_COMM_NAMES TABLE
PROTOCOL_INVS TABLE
DATABASE MODEL
ENTITIES TABLE
COUNTRY_REFS TABLE
LOCATION_STRUCTURE_REFS
DB_USERS TABLE
DB_USER_GROUPS TABLE
DB_USER_GRP_RIGHTS TABLE
DB_USER_RIGHTS TABLE
DB_OBJECTS TABLE
AGR_NOTIFICATIONS TABLE
DATABASE MODEL - DATABASE TABLE
DESCRIPTOR_CODES TABLE
ADDRESSES TABLE
INCOMING_ORDER_ANIMALS TABLE
ALLOCATION_RULES TABLE
COUNTRY_SUBDIVISION_REFS TABLE
TAX_CROSS_REFS TABLE
TAX_RELATIVES TABLE
DB_CODES TABLE
AGR_HISTORIES TABLE
INFO_SOURCE_REFS
TAX_PURPOSE_REFS
BIRTH_PLACE_REFS
UNIT_FORM_REFS
UNIT_SIZE_REFS
PROFESSION_REFS
DESCRIPTOR_CROSS_REFS TABLE
REQUEST_LINE_LOCS TABLE
SNP_FILE_FORMATS TABLE
SNP_FILE_COLUMNS TABLE
SNP_DATA_REQUESTS TABLE
SNP_DBSNP_REFERENCE_NUMBER TABLE
SNP_FILE_UPLOADS TABLE
SNP_FILE_ANIMLALS TABLE
IMAGES TABLE
DATABASE MODEL
NOTE: Up to date diagrams can be found in C:\srv\www\Animal-GRIN\doc\Animal GRIN Database Documents\Diagrams
The lowest level of the taxonomy structure for an individual will be linked to that individual/pedigree/or protocol record.
Any level of the taxonomy structure may have observations associated with it.
Any level of the taxonomy structure may have a species committee linked to it.
DATABASE MODEL - DATABASE TABLE
NOTE: Up to date diagrams can be found in C:\srv\www\Animal-GRIN\doc\Animal GRIN Database Documents\Diagrams
The lowest level of the taxonomy structure for an individual will be linked to that individual/pedigree/or protocol record.
Any level of the taxonomy structure may have observations associated with it.
Any level of the taxonomy structure may have a species committee linked to it.
TERMS
| TERMS | |
| agreement | a legally binding document that establishes a framework for cooperation and coordination between the USDA ARS NAGP and the contact or organization entering into the agreement. |
| core allocation | used as an emergency back up inventory. |
| dam | a female parent. |
| descriptor | the framework for an observation about an entity, an inventory, an individual, or a taxonomy structure. |
| descriptor codes | specific values that are allowable for an observation. |
| evaluation allocation | able to be used and requested by internal entities within the NAGP. |
| genomic | A subsystem which stores genotype results associated with animals. Meta-data is stored in the database. Because the actual data files are large they are stored in external files. Each file lists all SNPs for the snp_file_format in the order defined by the values of snp_file_columns.snp_file_format_id_ID values. |
| germplasm | 1. germ cells and their precursors serving as the bearers of heredity and being fundamentally independent of other cells. |
2. the hereditary material of the germ cells: genes
| observation | data and/or measurements about something. |
| pedigree | 1. a register recording a line of ancestors. |
2. an ancestral line; the origin and the history of something.
protocol 1. a detailed plan of a scientific or medical experiment, treatment, or procedure.
2. an original draft, minute, or record of a document or transaction.
| protocol | set of instructions related to the cryopreservation of material. |
| recipe | formula to make a diluting agent used in a protocol. |
| restricted allocation | restricted access to the inventory based on the associated agreement. |
| sire | a male parent. |
| taxonomy | 1. the study of the general principles of scientific classification. |
2. classification; especially orderly classification of plants and animals according to their presumed natural relationships.
working allocation able to be used and requested by external entities.
incoming_orders TABLE
| incoming_orders TABLE |
| This table stores the information about shipments of materials received. |
DEFINITION: An INCOMING_ORDER is a shipment of material (germplasm) provided to a repository by the owner (ENTITY) and sent to a repository by an ENTITY. (The germplasm owner and shipper may be two different entities.)
DELETION RULE: Records may be deleted ONLY if there are no inventory records attached.
This table does NOT share records.
| FIELD NAME | DATA TYPE | FIELD LENGTH | REQUIRED? | CODED? | NOTES | DESCRIPTION | |
| PK | id | INTEGER | N/A | Y | N | Auto-generated | Unique identifier for incoming orders. |
| incoming_order_type | VARCHAR | 50 | Y | Y - Donation (default), Field Collection, Contracted Collection, Purchase, Return | Any user may enter new values at anytime. | This field identifies how this material was provided, via a donation, a field collection, a contracted collection, a purchase, etc. |
Note: This drop down menu is NOT filtered by the DB_CODES table RECORD_SOURCE field.
| description | VARCHAR | 1000 | N | N | This field is available for details regarding this incoming order. For example, who was contracted, cost per straw, etc. | ||
| FK | germplasm_owner_id | INTEGER | N/A | Y | N | NO ACTION | This is the reference to the ID number of the ENTITIES which has provided the inventory to us. |
This FK is filtered by RECORD_SOURCE.
MySQL: INDEX - inc_orders_germplasm_ind FK shipper_id INTEGER N/A Y N NO ACTION This is the reference to the ENTITIES who shipped the sample to the repository.
This FK is filtered by RECORD_SOURCE.
MySQL: INDEX - inc_orders_shipper_ind FK repository_id INTEGER N/A Y N NO ACTION This is the reference to the ID number of the receiving repository site from the ENTITY table. (Must be a Entity_Type = "O" and it must have a REPOSITORY_FLAG = "Y")
This FK is filtered by RECORD_SOURCE.
MySQL: INDEX - inc_orders_repository_ind
| date_received | DATE | N/A | Y | N | DEFAULT (APP) to today's date. | The date the shipment was received at the repository. | |
| FK | def_breeder_id | INTEGER | N/A | N | N | NO ACTION | This field is in order to set up a default for animals who have inventory which has come in on this incoming order. This field is a reference to the breeder's Entity record. |
This FK is filtered by RECORD_SOURCE.
MySQL: INDEX - inc_orders_def_breeder_ind def_germplasm_type VARCHAR 30 N Y - Semen, Embryo, Oocyte, Whole Blood, Buffy Coat/White Blood Cells, Hair, Skin, Muscle, Liver, Spleen, Heart, Feather Pulp, Fibroblasts, Primordial Germ Cells, Blastodermal Cells, Testicular Tissue, Ovarian Tissue, Purified DNA, Umbilical Cord, Placenta, Bone Marrow Any user may enter new values at anytime. This field is in order to set up a default for inventory items which are attached to this incoming order. This field contains the germplasm type of the inventory.
Codes are defined by the inventories.germplasm_type db_object.
Note: This drop down menu is NOT filtered by the DB_CODES table RECORD_SOURCE field.
def_orig_state VARCHAR 15 N Y - Fresh, Cryo Only users with special privileges may enter new values at anytime. This field is in order to set up a default for inventory items which are attached to this incoming order. This field contains the original state of the inventory.
Codes are defined by the inventories.orig_state db_object.
Note: This drop down menu is NOT filtered by the DB_CODES table RECORD_SOURCE field.
FK def_unit_form_id INTEGER N/A N NO ACTION
Any user may enter new values at anytime. This field is in order to set up a default for inventory items which are attached to this incoming order. This field contains a reference to the UNIT_FORM_REFS table for the record storing the type of packaging in which the sample is.
This FK is NOT filtered by RECORD_SOURCE.
MySQL: INDEX - inc_orders_def_unitform_ind FK def_unit_size_id INTEGER N/A N NO ACTION
Any user may enter new values at anytime. This field is in order to set up a default for inventory items which are attached to this incoming order. This field contains a reference to the UNIT_SIZE_REFS table for the record storing the size of the container (straw, etc.) which is also the amount of sample in the container (for example, blood, semen).
This FK is NOT filtered by RECORD_SOURCE.
MySQL: INDEX - inc_orders_def_unitsize_ind FK def_protocol_id INTEGER N/A N ON DELETE SET NULL (APP) - if the referenced protocol record is deleted, this field should be blanked out. This field is in order to set up a default for inventory items which are attached to this incoming order. This is a reference to the protocol which has been used on this inventory. This will actually be entered into the PROTOCOL_INVS table with a reference to the specific inventory as well.
This FK is filtered by RECORD_SOURCE.
MySQL: INDEX - inc_orders_def_protocol_ind
| comments | VARCHAR | 100 | N | N | A field for entering comments regarding this INCOMING_ORDERS record. | ||
| created_by | VARCHAR | 30 | Y | N | Auto-generated | This is the user name of the user who created this record. | |
| created_at | DATETIME | N/A | Y | N | Auto-generated | This is the date and time this record was created. | |
| updated_by | VARCHAR | 30 | N | N | Auto-generated | This is the user name of the user who most recently updated this record. | |
| updated_at | DATETIME | N/A | N | N | Auto-generated | This is the most recent date and time this record was updated. | |
| record_source | CHAR | 2 | Y | N | Auto-generated | This is set to the value from COUNTRY_REFS table COUNTRY_ABBR field where the DB_USERS table COUNTRY_ID field value from the DB_USERS record for who created the record originally = COUNTRY_REFS table COUNTRY_ID. | |
| Return to Table of Contents | |||||||
| NOTES: |
Return to Table of Contents incoming_order_animals TABLE
| incoming_order_animals TABLE |
| This table provides the many-to-many relationship between incoming orders and animals contained in each order. |
DELETION RULE: Records may be deleted ONLY if there are no inventory records attached.
This table does NOT share records.
| FIELD NAME | DATA TYPE | FIELD LENGTH | REQUIRED? | CODED? | NOTES | DESCRIPTION | |
| PK | id | INTEGER | N/A | Y | N | Auto-generated | The unique identifier for each combination of INCOMING_ORDERS and ANIMALS. |
| FK | incoming_order_id | INTEGER | N/A | Y | N | ON DELETE CASCADE |
UNIQUE with incoming_order_id and animal_id A reference to the incoming order ID from the INCOMING ORDERS table.
This FK is filtered by RECORD_SOURCE.
MySQL: INDEX - inc_ord_anms_inc_ord_ind FK animal_id INTEGER N/A Y N NO ACTION
UNIQUE with incoming_order_id and animal_id A reference to the animal ID from the ANIMALS table.
This FK is filtered by RECORD_SOURCE.
MySQL: INDEX - inc_ord_anms_animal_ind
| created_by | VARCHAR | 30 | Y | N | Auto-generated | This is the user name of the user who created this record. | |
| created_at | DATETIME | N/A | Y | N | Auto-generated | This is the date and time this record was created. | |
| updated_by | VARCHAR | 30 | N | N | Auto-generated | This is the user name of the user who most recently updated this record. | |
| updated_at | DATETIME | N/A | N | N | Auto-generated | This is the most recent date and time this record was updated. | |
| record_source | CHAR | 2 | Y | N | Auto-generated | This is set to the value from COUNTRY_REFS table COUNTRY_ABBR field where the DB_USERS table COUNTRY_ID field value from the DB_USERS record for who created the record originally = COUNTRY_REFS table COUNTRY_ID. | |
| Return to Table of Contents | |||||||
| NOTES: | The INCOMING_ORDER_ID field and ANIMAL_ID field combination must be UNIQUE. |
Return to Table of Contents entities TABLE
| entities TABLE |
| This table stores information about a person or organization. |
DEFINITION: An entity is either a person or an organization.
DELETION RULE: Records may not be deleted, instead ENTITIES table INACTIVE_FLAG may be set to "Y".
This table does NOT share records.
| FIELD NAME | DATA TYPE | FIELD LENGTH | REQUIRED? | CODED? | NOTES | DESCRIPTION | ||
| PK | id | INTEGER | N/A | Y | N | Auto-generated. | This is an auto-created number that serves as the ID number for each entity record. | |
| organization_name | VARCHAR | 75 | N | N | This is the name of the organization. | |||
| repository_flag | CHAR | 1 | Y | Y - Y or N (Default) | No new codes may be entered. | |||
| DEFAULT 'N' | A flag indicating if this record is for a repository site from which requests can be made and material (germplasm) can be received. | |||||||
| name_prefix | VARCHAR | 12 | N | Y - Mrs., Mr., Miss, Ms., Dr., Mr. and Mrs. | Any user may enter new codes at anytime. | This is the prefix to the person's name. For example, Dr., Mrs., etc. |
Codes are defined by the entities.name_prefix db_object.
Note: This drop down menu is filtered by the DB_CODES table RECORD_SOURCE field.
| last_name | VARCHAR | 50 | N | N | This is the last name of the person. |
| first_name | VARCHAR | 50 | N | N | This is the first name(s) of the person. Multiple first names may be entered. For example, if there is a family you may enter all three first names here. Include any suffix and/or middle initials here. |
Standards needed to allow for searching.
FK profession_id INTEGER N/A N N NO ACTION
Any user may enter new codes at anytime. This is the profession of the person. For example, breeder. This is a reference to the PROFESSION_REFS table ID field for the appropriate profession record.
MySQL: INDEX - entities_profession_ind
Note: This drop down menu IS filtered by the DB_CODES table RECORD_SOURCE field.
FK organization_id INTEGER N/A N N NO ACTION The reference to the organization to which this person belongs, if any.
CHECK This field value cannot = ID field value.
This FK is filtered by RECORD_SOURCE.
MySQL: INDEX - entities_org_ind
| phone1 | VARCHAR | 30 | N | N | The primary phone number by which to contact the person or organization. | |
| phone2 | VARCHAR | 30 | N | N | The secondary phone number by which to contact the person or organization. | |
| VARCHAR | 50 | N | N | The email address by which to contact the person or organization. | ||
| fax | VARCHAR | 30 | N | N | The fax number by which to contact the person or organization. | |
| web_address | VARCHAR | 100 | N | N | This is the web address of the entity. | |
| comments | VARCHAR | 100 | N | N | Any comments relating to this entity. | |
| entity_type | CHAR | 1 | Y | Y - P (default) or O | No new codes may be entered. |
| DEFAULT 'P' | Identifies which type of record this is, PERSON or ORGANIZATION. | |||||||
| inactive_flag | CHAR | 1 | Y | Y - N (default) or Y | No new codes may be entered. | |||
| DEFAULT 'N' | This field allows an Entity record to be designated as inactive. | |||||||
| created_by | VARCHAR | 30 | Y | N | Auto-generated | This is the user name of the user who created this record. | ||
| created_at | DATETIME | N/A | Y | N | Auto-generated | This is the date and time this record was created. | ||
| updated_by | VARCHAR | 30 | N | N | Auto-generated | This is the user name of the user who most recently updated this record. | ||
| updated_at | DATETIME | N/A | N | N | Auto-generated | This is the most recent date and time this record was updated. | ||
| record_source | CHAR | 2 | Y | N | Auto-generated | This is set to the value from COUNTRY_REFS table COUNTRY_ABBR field where the DB_USERS table COUNTRY_ID field value from the DB_USERS record for who created the record originally = COUNTRY_REFS table COUNTRY_ID. | ||
| Return to Table of Contents | ||||||||
| NOTES: |
Return to Table of Contents addresses TABLE
| addresses TABLE |
| This table stores all of the address information for an ENTITY. |
DEFINITION: An address is the mailing and/or physical location of an ENTITY.
DELETION RULE: Records be deleted ONLY if there are no attached request header records.
This table does NOT share records.
| FIELD NAME | DATA TYPE | FIELD LENGTH | REQUIRED? | CODED? | NOTES | DESCRIPTION | |
| PK | id | INTEGER | N/A | Y | N | Auto-generated. | This is an auto-created number that serves as the unique identifier for each address. |
| FK | entity_id | INTEGER | N/A | Y | N | NO ACTION | This is the reference to the ENTITIES record to which this address belongs. |
MySQL: INDEX - addresses_entity_ind
This FK is filtered by RECORD_SOURCE.
address_type VARCHAR 30 N Y - Ship To, Primary This field allows an address record to be designated as a specific type of address.
Codes are defined by the addresses.address_type db_object.
Note: This drop down menu is NOT filtered by the DB_CODES table RECORD_SOURCE field.
| address1 | VARCHAR | 50 | N | N | First address line. | ||
| address2 | VARCHAR | 50 | N | N | Second address line. | ||
| address3 | VARCHAR | 50 | N | N | Third address line. | ||
| city | VARCHAR | 50 | N | N | City. | ||
| FK | subdivision_id | INTEGER | N/A | N | N | NO ACTION | This is the reference to the COUNTRY_SUBDIVISION_REFS table record for the appropriate state/province. |
MySQL: INDEX - addresses_sub_ind
This FK is NOT filtered by RECORD_SOURCE.
FK country_id INTEGER N/A Y N NO ACTION This is the reference to the COUNTRY_REFS table record for the appropriate country.
MySQL: INDEX - addresses_ctry_ind
This FK is NOT filtered by RECORD_SOURCE.
| postal_code | VARCHAR | 20 | N | N | The postal code. | ||
| comments | VARCHAR | 100 | N | N | Any comments relating to this address. | ||
| created_by | VARCHAR | 30 | Y | N | Auto-generated | This is the user name of the user who created this record. | |
| created_at | DATETIME | N/A | Y | N | Auto-generated | This is the date and time this record was created. | |
| updated_by | VARCHAR | 30 | N | N | Auto-generated | This is the user name of the user who most recently updated this record. | |
| updated_at | DATETIME | N/A | N | N | Auto-generated | This is the most recent date and time this record was updated. | |
| record_source | CHAR | 2 | Y | N | Auto-generated | This is set to the value from COUNTRY_REFS table COUNTRY_ABBR field where the DB_USERS table COUNTRY_ID field value from the DB_USERS record for who created the record originally = COUNTRY_REFS table COUNTRY_ID. | |
| Return to Table of Contents | |||||||
| NOTES: |
Return to Table of Contents ent_observations TABLE
| ent_observations TABLE |
| This table stores observations associated with an entity. This will usually be with regard to the environment. |
DEFINITION: An entity observation is information that has been collected regarding a specific person or organization.
DELETION RULE: Records may be deleted.
This table does NOT share records.
| FIELD NAME | DATA TYPE | FIELD LENGTH | REQUIRED? | CODED? | NOTES | DESCRIPTION | |
| PK | id | INTEGER | N/A | Y | N | Auto-generated | The unique identifier for the entity observation record. |
| FK | entity_id | INTEGER | N/A | Y | N | NO ACTION | The reference to the ENTITIES about which this observation was made. |
MySQL: INDEX - ent_obs_entity_ind
This FK is filtered by RECORD_SOURCE.
FK descriptor_id INTEGER N/A Y N NO ACTION The reference to the specific descriptor upon which this observation is based.
MySQL: INDEX - ent_obs_descriptor_ind
This FK is NOT filtered by RECORD_SOURCE.
FK info_source_id INTEGER N/A N N NO ACTION
Any user may enter new codes at anytime. The reference to the INFO_SOURCE_REFS table record containing the source of the information provided for this observation.
This FK is filtered by RECORD_SOURCE.
MySQL: INDEX - ent_obs_infosource_ind date_observed VARCHAR 10 N N The date, or portion of date, that this observation was made.
Note: This field is a VARCHAR because we may only know a portion of the date and it will not be used for calculations.
| obs_value | VARCHAR | 100 | N | N | Need to force validation based on formats entered for the selected descriptor. | The value of this observation. | |
| FK | obs_coded_value_id | INTEGER | N/A | N | N | NO ACTION | This field is used when the selected descriptor is a coded descriptor and the user selects a value. This references the DESCRIPTOR_CODES record for the option selected. |
This FK is NOT filtered by RECORD_SOURCE.
MySQL: INDEX - ent_obs_codedvalue_ind
| comments | VARCHAR | 100 | N | N | The comments pertaining to this observation. | ||
| created_by | VARCHAR | 30 | Y | N | Auto-generated | This is the user name of the user who created this record. | |
| created_at | DATETIME | N/A | Y | N | Auto-generated | This is the date and time this record was created. | |
| updated_by | VARCHAR | 30 | N | N | Auto-generated | This is the user name of the user who most recently updated this record. | |
| updated_at | DATETIME | N/A | N | N | Auto-generated | This is the most recent date and time this record was updated. | |
| record_source | CHAR | 2 | Y | N | Auto-generated | This is set to the value from COUNTRY_REFS table COUNTRY_ABBR field where the DB_USERS table COUNTRY_ID field value from the DB_USERS record for who created the record originally = COUNTRY_REFS table COUNTRY_ID. | |
| Return to Table of Contents | |||||||
| NOTES: | **Examples: latitude, longitude, elevation, climate, production, etc… |
Return to Table of Contents agreements TABLE
| agreements TABLE |
| This table stores the agreement information for all agreements made between the repository and the germplasm providing entity. Not all will have agreements. |
DEFINITION: An agreement is a legal document entered into by a person or organization and a repository detailing the arrangement between the two entities and any legal restrictions placed on germplasm provided by the entity.
DELETION RULE: Records may not be deleted, instead the AGREEMENTS table TERMINATION_DATE field may be filled in with the current date.
This table does NOT share records.
| FIELD NAME | DATA TYPE | FIELD LENGTH | REQUIRED? | CODED? | NOTES | DESCRIPTION | |
| PK | id | INTEGER | N/A | Y | N | Auto-generated. | This is the auto-generated ID number for the agreement. |
| document_num | VARCHAR | 30 | N | N | This is the official document number associated with this agreement. | ||
| established_date | DATE | N/A | N | N | The date as of which this agreement was established. | ||
| amended_date | DATE | N/A | N | N | The date when this agreement was amended. | ||
| termination_date | DATE | N/A | N | N | The date as of which this agreement was terminated. | ||
| FK | entity_id | INTEGER | N/A | Y | N | NO ACTION | The reference to the ENTITY with whom this agreement was made. |
MySQL: INDEX - agreements_entity_ind
This FK is filtered by RECORD_SOURCE.
FK repository_id INTEGER N/A Y N NO ACTION The reference to the ENTITY who is the repository with whom this agreement was made.
MySQL: INDEX - agreements_repository_ind
This FK is filtered by RECORD_SOURCE.
| details | VARCHAR | 2000 | Y | N | This field contains details regarding the agreement. | ||
| doc_image_url | VARCHAR | 100 | N | N | This is the address to the image of the scanned agreement document. | ||
| created_by | VARCHAR | 30 | Y | N | Auto-generated | This is the user name of the user who created this record. | |
| created_at | DATETIME | N/A | Y | N | Auto-generated | This is the date and time this record was created. | |
| updated_by | VARCHAR | 30 | N | N | Auto-generated | This is the user name of the user who most recently updated this record. | |
| updated_at | DATETIME | N/A | N | N | Auto-generated | This is the most recent date and time this record was updated. | |
| record_source | CHAR | 2 | Y | N | Auto-generated | This is set to the value from COUNTRY_REFS table COUNTRY_ABBR field where the DB_USERS table COUNTRY_ID field value from the DB_USERS record for who created the record originally = COUNTRY_REFS table COUNTRY_ID. | |
| Return to Table of Contents | |||||||
| NOTES: |
Return to Table of Contents inventories TABLE
| inventories TABLE |
| This table stores the inventory information. This is essentially the line item information for an ANIMAL contained in an INCOMING_ORDER. This also stores the allocation distribution of the inventory among the allocation categories: Core, Evaluation, Restricted, and Working. |
DEFINITION: An inventory is one germplasm type from one animal from one incoming order.
DELETION RULE: Records may be deleted ONLY if there have been no request_line_locs which reference any of the attached location records.
This table does NOT share records.
| FIELD NAME | DATA TYPE | FIELD LENGTH | REQUIRED? | CODED? | NOTES | DESCRIPTION | |
| PK | id | INTEGER | N/A | Y | N | Auto-generated | This is an auto-generated number for the line item of the incoming order. |
| FK | incoming_order_animal_id | INTEGER | N/A | Y | N | NO ACTION | This is a reference to the ID from the INCOMING_ORDER_ANIMALS table. |
MySQL: INDEX - inventories_inc_ord_anm_ind
This FK is filtered by RECORD_SOURCE.
germplasm_type VARCHAR 30 Y Y - Semen, Embryo, Oocyte, Whole Blood, Buffy Coat/White Blood Cells, Hair, Skin, Muscle, Liver, Spleen, Heart, Feather Pulp, Fibroblasts, Primordial Germ Cells, Blastodermal Cells, Testicular Tissue, Ovarian Tissue, Purified DNA, Umbilical Cord, Placenta, Bone Marrow Any user may enter new codes at anytime. This is the type of sample this is. For example, semen, blood, etc.
Codes are defined by the inventories.germplasm_type db_object.
Note: This drop down menu is NOT filtered by the DB_CODES table RECORD_SOURCE field.
FK unit_form_id INTEGER N/A Y N NO ACTION
Any user may enter new codes at anytime. This is the type of packaging in which the sample is. This is a reference to the UNIT_FORM_REFS table record for the appropriate type of packaging.
This FK is NOT filtered by RECORD_SOURCE.
MySQL: INDEX - inventories_unitform_ind orig_state VARCHAR 15 Y Y - Fresh, Cryo Only users with special privileges may enter new codes at anytime. This is the state of the sample upon receipt. For example, fresh, cryopreserved, etc.
Codes are defined by the inventories.orig_state db_object.
Note: This drop down menu is NOT filtered by the DB_CODES table RECORD_SOURCE field.
FK unit_size_id INTEGER N/A N N NO ACTION
Any user may enter new codes at anytime. This is the size of the container (straw, etc.) which is also the amount of sample in the container (for example, blood, semen). This is a reference to the UNIT_SIZE_REFS table record for the appropriate unit size.
This FK is NOT filtered by RECORD_SOURCE.
MySQL: INDEX - inventories_unitsize_ind
| collection_date | DATE | N/A | N | N | Date the inventory was collected | ||
| inv_label_txt | VARCHAR | 30 | N | N | This is the text from the label if the inventory has been frozen by a repository. | ||
| quantity | INTEGER | N/A | N | N | This is the total number of units (straws etc.) for this inventory. This is calculated by taking the sum of the beg_quantity * beg_unit_count (unless it is null, then * 1) of all location records for this inventory record. | ||
| core | INTEGER | N/A | N | N | Default based on Allocation Rules | The number of units which have been allocated to the Core category from this inventory. | |
| evaluation | INTEGER | N/A | N | N | Default based on Allocation Rules | The number of units which have been allocated to the Evaluation category from this inventory. | |
| restricted | INTEGER | N/A | N | N | Default based on Allocation Rules | The number of units which have been allocated to the Restricted category from this inventory. | |
| working | INTEGER | N/A | N | N | Default based on Allocation Rules | The number of units which have been allocated to the Working category from this inventory. | |
| comments | VARCHAR | 100 | N | N | This is available for comments regarding this sample. | ||
| created_by | VARCHAR | 30 | Y | N | Auto-generated | This is the user name of the user who created this record. | |
| created_at | DATETIME | N/A | Y | N | Auto-generated | This is the date and time this record was created. | |
| updated_by | VARCHAR | 30 | N | N | Auto-generated | This is the user name of the user who most recently updated this record. | |
| updated_at | DATETIME | N/A | N | N | Auto-generated | This is the most recent date and time this record was updated. | |
| record_source | CHAR | 2 | Y | N | Auto-generated | This is set to the value from COUNTRY_REFS table COUNTRY_ABBR field where the DB_USERS table COUNTRY_ID field value from the DB_USERS record for who created the record originally = COUNTRY_REFS table COUNTRY_ID. | |
| Return to Table of Contents | |||||||
| NOTES: | |||||||
| For a detailed explaination of how to handle Embryos, please double click the following link. |
Return to Table of Contents locations TABLE
| locations TABLE |
| This table stores one level of detail for a specific location. For example, the pie information for a specific set of locations. |
DEFINITION: A location is one level in a location structure in which a quantity from one inventory is stored.
DELETION RULE: Records may be deleted ONLY if there are no attached request line locs records.
This table does NOT share records.
| FIELD NAME | DATA TYPE | FIELD LENGTH | REQUIRED? | CODED? | NOTES | DESCRIPTION | |
| PK | id | INTEGER | N/A | Y | N | Auto-generated | This is an auto-generated number assigned to each location record. |
| FK | inventory_id | INTEGER | N/A | N | N | ON DELETE CASCADE | This is a reference to the inventory which is stored in this location. |
This is required at the bottom level of the location structure only, where SUBLEVEL_FLAG = "N", and is not populated at any other point in the structure.
This FK is filtered by RECORD_SOURCE.
MySQL: INDEX - locations_inventory_ind container_type VARCHAR 30 Y Y - Cane, Goblet, Vial, Visotube Only users with special privileges may enter new codes at anytime. This is the type of container in which the inventory is stored.
Codes are defined by the locations.container_type db_object.
Note: This drop down menu is NOT filtered by the DB_CODES table RECORD_SOURCE field.
loc_level VARCHAR 30 Y Y - Tank, Pie, Pie Section, Canister, Goblet, Visotube, Vial, Cane Only users with special privileges may enter new codes at anytime. This is the level of this location. For example, if it is the Canister location for a goblet it would be Goblet.
Codes are defined by the locations.loc_level db_object.
Note: This drop down menu is NOT filtered by the DB_CODES table RECORD_SOURCE field.
| loc_value | VARCHAR | 5 | Y | N | This is the number or letter of the location. For example, canister number "5", or visotube "A". | |
| beg_quantity | INTEGER | N/A | N | N | Either beg_quantity or beg_unit_count MUST = 1; either cur_quantity or cur_unit_count MUST = 1; |
In the application, the CUR_QUANTITY and CUR_UNIT_COUNT fields default to whatever was entered in the BEG_QUANTITY and the BEG_UNIT_COUNT fields when a new record is being created. This is the beginning number of straws stored in this location (usually only at the lowest level of location).
**This is required at the lowest level of the location structure.** cur_quantity INTEGER N/A N N This is the current number of straws, for example, stored in this location (usually only at the lowest level of location). This field should default to the beg_quantity value when the record is created. Then this value is updated as requests are filled removing inventory from this location.
**This is required at the lowest level of the location structure.**
| beg_unit_count | INTEGER | N/A | N | N | This is used to show the number of units (could be pellets in a vial, or embroys in a straw, for example) which are stored in the location initially. | |
| cur_unit_count | INTEGER | N/A | N | N | This is used to show the number of units (could be pellets in a vial, or embroys in a straw, for example) which are stored in the location currently. This field should default to the beg_unit_count value when the record is created. Then this value is updated as requests are filled removing inventory from this location. | |
| quality | VARCHAR | 30 | N | Y - 1: Excellent or good, 2: Fair, 3: Poor, 4: Dead or degenerating | Codes may only be added by users with special privileges on a special form. | This is the quality of the inventory. |
Codes are defined by the locations.quality db_object.
Note: This drop down menu is NOT filtered by the DB_CODES table RECORD_SOURCE field.
stage VARCHAR 30 N Y Codes may only be added by users with special privileges on a special form. This is the stage of the embryo.
Codes are defined by the locations.stage db_object.
Note: This drop down menu is NOT filtered by the DB_CODES table RECORD_SOURCE field.
FK parent_loc_id INTEGER N/A N N NO ACTION This is the reference to the parent location level ID. For example, in the above mentioned example this field would contain the ID for the Pie location record.
CHECK This field value cannot = ID field value.
This FK is filtered by RECORD_SOURCE.
MySQL: INDEX - locations_parent_loc_ind
| sublevel_flag | CHAR | 1 | Y | Y - Y or N (Default) | No new codes may be entered. | |||
| DEFAULT 'N' | This indicates if there is a sublevel below this current location in the location structure. | |||||||
| created_by | VARCHAR | 30 | Y | N | Auto-generated | This is the user name of the user who created this record. | ||
| created_at | DATETIME | N/A | Y | N | Auto-generated | This is the date and time this record was created. | ||
| updated_by | VARCHAR | 30 | N | N | Auto-generated | This is the user name of the user who most recently updated this record. | ||
| updated_at | DATETIME | N/A | N | N | Auto-generated | This is the most recent date and time this record was updated. | ||
| record_source | CHAR | 2 | Y | N | Auto-generated | This is set to the value from COUNTRY_REFS table COUNTRY_ABBR field where the DB_USERS table COUNTRY_ID field value from the DB_USERS record for who created the record originally = COUNTRY_REFS table COUNTRY_ID. | ||
| Return to Table of Contents | ||||||||
| NOTES: | FOR AN EXAMPLE OF A LOCATION STRUCTURE SET OF RECORDS, PLEASE DOUBLE-CLICK |
THIS FILE.
Return to Table of Contents allocation_rules TABLE
| allocation_rules TABLE |
| This table allows rules to be set up regarding the allocation based on the entity, incoming_order, animal, or taxonomy structure. This will be used to create the default allocation record which can then be accepted or overridden by the user when entering the inventory into the database. |
DEFINITION: An allocation rule is a rule detailing the way to allocate inventory among the allocation categories. These rules create the defaulted allocation for an inventory, which can then be changed by the user on the form.
DELETION RULE: Records may only be deleted if they have not been used to allocated inventory (meaning there is no record in the ALLOCATION_RULE_INVENTORIES table), otherwise the ALLOCATION_RULES table TERMINATION_DATE field may be populated with the current date.
This table does NOT share records.
| FIELD NAME | DATA TYPE | FIELD LENGTH | REQUIRED? | CODED? | NOTES | DESCRIPTION | |
| PK | id | INTEGER | N/A | Y | N | Auto-generated | This is the auto-generated number for this allocation rule. |
| rule_type | CHAR | 3 | Y | Y - ENT, INC, ANM, TAX | No new codes may be added. |
| CHECK | This field indicates to which table the reference ID refers. | |||||||
| FK | animal_id | INTEGER | N/A | N | N | ON DELETE CASCADE, WILL NEED TO END DATE THE RULE RECORD IF THIS RULE HAS BEEN USED ON INVENTORY - CANNOT BE DELETED (TRIGGER). | This field references the appropriate record in the ANIMALS table. |
This FK is filtered by RECORD_SOURCE.
MySQL: INDEX - allo_rules_animal_ind FK entity_id INTEGER N/A N N ON DELETE NO ACTION, ALSO NEED TO END DATE THE RULE RECORD (TRIGGER). This field references the appropriate record in the ENTITIES table.
This FK is filtered by RECORD_SOURCE.
MySQL: INDEX - allo_rules_entity_ind FK incoming_order_id INTEGER N/A N N ON DELETE CASCADE, WILL NEED TO END DATE THE RULE RECORD IF THIS RULE HAS BEEN USED ON INVENTORY - CANNOT BE DELETED (TRIGGER). This field references the appropriate record in the INCOMING_ORDERS table.
This FK is filtered by RECORD_SOURCE.
MySQL: INDEX - allo_rules_inc_ord_ind FK taxonomy_id INTEGER N/A N N ON DELETE CASCADE, WILL NEED TO END DATE THE RULE RECORD IF THIS RULE HAS BEEN USED ON INVENTORY - CANNOT BE DELETED (TRIGGER). This field references the appropriate record in the TAXONOMIES table.
This FK is filtered by RECORD_SOURCE.
MySQL: INDEX - allo_rules_tax_ind
| effective_date | DATE | N/A | Y | N | The date on which this rule is effective. | |
| duration | DECIMAL | N/A | N | N | Measured in Years. | The duration of this allocation rule in years. |
| termination_date | DATE | N/A | N | N | The last date on which this rule was amended. | |
| allocation_value | INTEGER | N/A | Y | N | The value which is to be assigned to the specified allocation category. | |
| rule_priority | INTEGER | N/A | Y | N | This field is used in cases where there are multiple rules needed in order to allocate things properly to specify which rule is to be executed first. |
For example, if the rule is that "All germplasm is Restricted, except 10 units per bull for Evaluation". Then two rules would be entered, one stating that 10 units is to go to Evaluation, which would have a priority of 1, and then the second rule saying 100% goes to Restricted, which would have a priority of 2.
value_type VARCHAR 30 Y Y - Units, Percent Only users with special privileges may enter new codes at anytime. The type of value. For example, percent or units.
Codes are defined by the allocation_rules.value_type db_object.
Note: This drop down menu is NOT filtered by the DB_CODES table RECORD_SOURCE field.
germplasm_type VARCHAR 30 Y Y - Semen, Embryo, Oocyte, Whole Blood, Buffy Coat/White Blood Cells, Hair, Skin, Muscle, Liver, Spleen, Heart, Feather Pulp, Fibroblasts, Primordial Germ Cells, Blastodermal Cells, Testicular Tissue, Ovarian Tissue, Purified DNA Any user may enter new codes at anytime. The type of germplasm to which this rule applies. For example, semen, blood, etc.
Codes are defined by the inventories.germplasm_type db_object.
Note: This drop down menu is NOT filtered by the DB_CODES table RECORD_SOURCE field.
allocation_category VARCHAR 10 Y Y - Core, Evaluation, Restricted, Working No new codes may be added.
| CHECK | This is the allocation category to which the specified value is to be allocated. This can be Core, Working, Evaluation, or Restricted. | |||||||
| FK | agreement_id | INTEGER | N/A | N | N | NO ACTION | The reference to the agreement, if there is one, on which this allocation rule is based. |
This FK is filtered by RECORD_SOURCE.
MySQL: INDEX - allo_rules_agreement_ind
| comments | VARCHAR | 100 | N | N | Comments regarding this allocation rule. | ||
| created_by | VARCHAR | 30 | Y | N | Auto-generated | This is the user name of the user who created this record. | |
| created_at | DATETIME | N/A | Y | N | Auto-generated | This is the date and time this record was created. | |
| updated_by | VARCHAR | 30 | N | N | Auto-generated | This is the user name of the user who most recently updated this record. | |
| updated_at | DATETIME | N/A | N | N | Auto-generated | This is the most recent date and time this record was updated. | |
| record_source | CHAR | 2 | Y | N | Auto-generated | This is set to the value from COUNTRY_REFS table COUNTRY_ABBR field where the DB_USERS table COUNTRY_ID field value from the DB_USERS record for who created the record originally = COUNTRY_REFS table COUNTRY_ID. | |
| Return to Table of Contents | |||||||
| NOTES: | Allocation Rules are specific to country and are to be applied across all repositories within that country. | ||||||
| FOR A PRESENTATION DESCRIBING HOW THE ALLOCATION RULES WILL BE ENTERED AND USED, |
PLEASE DOUBLE-CLICK THIS FILE.
Need a loader to be added to the forms in order to make the process of entering these rules easier.
Return to Table of Contents allocation_rule_inventories
| allocation_rule_inventories TABLE |
| This table provides a way to track which rule was used for the allocation of a specific inventory and when. |
DELETION RULE: Records may only be deleted if the attached inventory record is deleted.
This table does NOT share records.
| FIELD NAME | DATA TYPE | FIELD LENGTH | REQUIRED? | CODED? | NOTES | DESCRIPTION | |
| PK | id | INTEGER | N/A | Y | N | Auto-generated | This is the auto-generated number for this allocation rule inventory intersection. |
| FK | allocation_rule_id | INTEGER | N/A | Y | N | NO ACTION | This field references the appropriate record in the ALLOCATION_RULES table. |
This FK is filtered by RECORD_SOURCE.
MySQL: INDEX - allo_rule_inv_allo_rule_ind FK inventory_id INTEGER N/A Y N ON DELETE CASCADE This field references the appropriate record in the INVENTORIES table.
This FK is filtered by RECORD_SOURCE.
MySQL: INDEX - allo_rule_inv_inventory_ind
| date_applied | DATE | N/A | Y | N | The date on which this rule was used to allocate the inventory specified. | ||
| allocated_quantity | INTEGER | N/A | Y | N | The actual quantity allocated. | ||
| expired_allocation_removed | CHAR | 1 | N | Y - Y/N | Indicates that the associated inventory has had allocation reduced by the allocation rule quantity | ||
| unallocated_quantity | INTEGER | N/A | N | N | The amount inventory allocations were reduced by when the allocation rule expired | ||
| created_by | VARCHAR | 30 | Y | N | Auto-generated | This is the user name of the user who created this record. | |
| created_at | DATETIME | N/A | Y | N | Auto-generated | This is the date and time this record was created. | |
| updated_by | VARCHAR | 30 | N | N | Auto-generated | This is the user name of the user who most recently updated this record. | |
| updated_at | DATETIME | N/A | N | N | Auto-generated | This is the most recent date and time this record was updated. |
This is the start of the file's text. The full file is on GovTribe.
File details come from the government source that posted it. Updated .