Table Overview

The data in Babase are stored in tables. Tables can be visualized as grids, with rows and columns. Each row represents a single real-world thing or event, an entity, e.g. a baboon. Each cell in the row contains a single unit of information, e.g. a birth date, a name, and a sex. The row holds the entirety of the information belonging to the entity as an isolated thing, e.g. baboon database entities consist of a birth date, a name, and a sex. Each column contains one and only one kind of information, e.g. birth date.

Table 2.1 is an example of a database table that might be used to represent baboons, one baboon per row. Notice that each cell contains one and exactly one unit of information.

Table 2.1. A Simple Database Table

Birth Name Sex
May 23, 1707 Alice Female
February 12, 1809 Bob Male
July 22, 1822 Carol Female

Anyone working with Babase will require a familiarity with the database's tables. An understanding of the entity each row represents is critical when working with a table. The tables below provide short definitions of the entities each The babase schema table holds in its rows.

Some of the tables in Babase exist to define a vocabulary. These are the support tables. For lack of a better term, the remainder of the tables are labeled main tables in Table 2.2.

Warning

Tables which have names ending in _DATA should not be used, there is always a view of the data in these tables that may be used in their place. Tables ending in _DATA may change in future Babase minor releases, breaking queries and programs which use the table. Use of the corresponding views will ensure compatibility with future Babase releases.

Table 2.2. The Main Babase Tables

Group Membership and Life Events
Table One row for each
ALTERNATE_SNAMES rescinded sname
BIOGRAPH animal, including fetuses
CENSUS day each individual is (or is not) observed in a group
CONSORTDATES male who has a known first consortship
DEMOG mention of an individual's presence in a group within a field textual note
DISPERSEDATES male who has left his maternal study group
GROUPS group (including solitary males)
MATUREDATES individual who is sexually mature
RANKDATES individual[a] who has attained adult rank
 
Analyzed: Group Membership and Life Events
Table One row for each
DAD_DATA offspring having a paternity analysis
MEMBERS day each individual is alive
RANKS month each individual is ranked in each group
Physical Traits
Table One row for each
WP_AFFECTEDPARTS body part affected by a specific wound/pathology
WP_DETAILS wound or pathology cluster indicated on a report
WP_HEALUPDATES update on progress of wound/pathology healing
WP_REPORTS wound/pathology report
 
Analyzed: Physical Traits
Table One row for each
HORMONE_KITS kit or protocol used to assay hormone concentration
HORMONE_PREP_DATA laboratory preparation performed on a sample in the specified series
HORMONE_PREP_SERIES series of preparations and assays performed on a sample
HORMONE_RESULT_DATA assay for hormone concentration in a sample
HORMONE_SAMPLE_DATA tissue sample used in hormone analysis
HYBRIDGENE_ANALYSES analysis of genetic hybrid scores
HYBRIDGENE_SCORES genetic hybrid score for an individual from an analysis
 
Sexual Cycles
Table One row for each
CYCGAPS female for each initiation or cessation of a continuous period of observation
CYCLES female's cycle (complete or not)
CYCPOINTS Mdate (menses), Tdate (turgesence onset), or Ddate (deturgesence onset) date of each female
PREGS time a female becomes pregnant
SEXSKINS sexskin measurement of each female
 
The Sexual Cycle Day-By-Day Tables
Table One row for each
CYCGAPDAYS female for each day within a period during which there is not continuous observation
CYCSTATS day each female is cycling -- by M, T and Ddates
MDINTERVALS day each female is cycling and is between M and Ddates
MMINTERVALS day each female is cycling -- by Mdates
REPSTATS day each female has a known reproductive state
 
Social and Multiparty Interactions
Table One row for each
ALLMISCS free form all-occurrences datum
CONSORTS multiparty dispute over a consortship
FPOINTS point observation of a mature female
INTERACT_DATA interaction between individuals
MPIS collection of multiparty interactions
MPI_DATA single dyadic interaction of a multiparty interaction collection
MPI_PARTS participant in a dyadic interaction of a multiparty interaction collection
PARTS participant in each interaction
POINT_DATA individual point observation
NEIGHBORS neighbor recorded in each point sample
SAMPLES all-occurrences sample
 
Darting
Table One row for each
ANESTHS time additional sedation is administered to a darted individual
BODYTEMPS body temperature measurement taken of a darted individual
CHESTS chest circumference measurement made of a darted individual
CROWNRUMPS crown to rump measurement made of a darted individual
DART_SAMPLES sample type collected at each darting
DARTINGS darting of an animal when data was collected
DPHYS darting event during which physiological measurements were taken
HUMERUSES humerous length measurement made of a darted individual
PCVS packed cell volume measurement taken from a darted individual
TEETH possible tooth site within the mouth on which data was collected for every darting event during which dentition data was collected
TESTES_ARC every testicle width/length measurement recorded, as measured along a portion of the circumference
TESTES_DIAM every testicle width/length measurement recorded, as measured along the diameter
TICKS darting event during which data on ticks and other parasites were recorded
ULNAS ulna length measurement made of a darted individual
 
Analyzed: Darting
Table One row for each
WBC_COUNTS count from a blood smear collected during a darting
 
Inventory
Table One row for each
LOCATIONS Location that can be used to store tissue and nucleic acid samples
NUCACID_CONC_DATA Quantification of a nucleic acid sample's concentration
NUCACID_DATA Nucleic acid sample that is or ever has been in the inventory
NUCACID_LOCAL_IDS Name/ID used to identify a nucleic acid sample at a particular institution
NUCACID_SOURCES Nucleic acid sample that has another nucleic acid sample as its source
POPULATIONS Study population under observation or from which tissue or nucleic acid samples have been collected
TISSUE_DATA Tissue sample that is or ever has been in the inventory
TISSUE_LOCAL_IDS Name/ID used to identify a tissue sample at a particular institution
UNIQUE_INDIVS Individual under observation or from whom tissue or nucleic acid samples have been collected
 
SWERB Data (Group-level Geolocation Data)
Table One row for each
AERIALS aerial photo used for map quadrant specification
GPS_UNITS GPS device
QUAD_DATA SWERB map quadrant
SWERB_BES uninterrupted bout of group-level observation
SWERB_DATA event related to group-level geolocation
SWERB_DEPARTS_DATA departure from camp of a observation team which collected SWERB data
SWERB_GWS geolocated physical object (grove or waterhole)
SWERB_GW_LOC_DATA recorded location of a geolocated physical object (grove or waterhole)
SWERB_LOC_DATA observation of a group at a time at a geolocated physical object
SWERB_LOC_DATA_CONFIDENCES analyzed observation of a location
SWERB_LOC_GPS observation of a group at a time at a geolocated physical object made using gps units and a protocol that requires 2 waypoint readings
SWERB_OBSERVERS departure from camp of an observer who drove or collected SWERB data
 
Weather Data
Table One row for each
RAINGAUGES rain gauge reading
RGSETUPS rain gauge installation
TEMPMAXS maximum temperature reading
TEMPMINS minimum temperature reading
WEATHERHAWK weather reading reported by the WeatherHawk instruments
WREADINGS manually collected meteorological data collection event
 

[a] At this time of this writing only males have data entered into RANKDATES.


The significant aspects of the the support tables are: the Id column -- the name of the column holding the vocabulary term, which columns of which tables use the vocabulary, and what sort of vocabulary the table defines. Table 2.3 summarizes this information.

Note

The Id columns throughout Babase do not allow values that are NULL, or which are textual but contain no characters, or which consist solely of spaces.

Table 2.3. The Babase Support Tables

General Support Tables
Table Id Column Related Column(s) One entry for every possible choice of...
BODYPARTS Bodypart TICKS.Bodypart, BODYPARTS.Bodyregion, WP_AFFECTEDPARTS.Bodypart part of the body
LAB_PERSONNEL Initials HYBRIDGENE_ANALYSES.Analyzed_By, NUCACID_CREATORS.Creator, WBC_COUNTS.Counted_By person who generates data, usually in a lab setting
OBSERVERS Initials SAMPLES.Observer, WREADINGS.WRperson, RGSETUPS.RGSPerson, CROWNRUMPS.CRobserver, CHESTS.Chobserver, ULNAS.Ulobserver, HUMERUSES.Huobserver, SWERB_OBSERVERS.Observer person who record observational data
OBSERVER_ROLES Initials OBSERVERS.Role, OBSERVERS.SWERB_Observer_Role, OBSERVERS.SWERB_Driver_Role, SWERB_OBSERVERS.Role way in which a person can be involved in the data collection process
UNKSNAMES Unksname NEIGHBORS.Unksname and the SWERB_UPLOAD view problem in identifying neighbor of focal during point sampling or in identifying a lone male in a SWERB other group observation
 
Group Membership and Life Events
Table Id Column Related Column(s) One entry for every possible choice of...
BSTATUSES Bstatus BIOGRAPH.Bstatus birthday estimation accuracy
CONFIDENCES Confidence BIOGRAPH.DcauseNatureConfidence, BIOGRAPH.DcauseAgentConfidence, DISPERSEDATES.Dispconfidence, BIOGRAPH.Matgrpconfidence degree of certitude in nature of death, agent of death, disperse date assignment, or maternal group assignment
DAD_SOFTWARE Software DAD_DATA.Software software package used to perform genetic paternity analysis
DCAUSES Dcause BIOGRAPH.Dcause cause of death
DEATHNATURES Nature DCAUSES.Nature reason for death
DEMOG_REFERENCES Reference DEMOG.Reference data source for demography notes
MSTATUSES Mstatus MATUREDATES.Matured, RANKDATES.Ranked maturity marker date estimation process
DAD_DATA_COMPLETENESS Completeness DAD_DATA.Completeness category of analysis completeness
DAD_DATA_MISMATCHES Mismatch DAD_DATA.Consensus_Mismatch category of genetic mismatch
RNKTYPES Rnktype RANKS.Rnktype rank ordering assigned to subject and month
STATUSES Status BIOGRAPH.Status baboon alive at last observation
 
Physical Traits
Table Id Column Related Column(s) One entry for every possible choice of...
HORMONE_IDS Hormone HORMONE_KITS.Hormone hormone that may be extracted and assayed for
HORMONE_PREP_PROCEDURES Id HORMONE_PREP_DATA.Procedure procedure that may be performed in preparation for a hormone assay
HYBRIDGENE_SOFTWARE Software HYBRIDGENE_ANALYSES.Software software used for genetic hybrid score analysis
MARKERS Marker HYBRIDGENE_ANALYSES.Marker type of genetic marker used for genetic hybrid score analysis
WP_HEALSTATUSES Healstatus WP_HEALUPDATES.HealStatus healing progress used in healing updates
WP_REPORTSTATES ReportState WP_REPORTS.ReportState status of wound/pathology report
WP_WOUNDPATHCODES WoundPathCode WP_DETAILS.WoundPathCode wound or pathology
 
Social and Multiparty Interactions
Table Id Column Related Column(s) One entry for every possible choice of...
ACTIVITIES Activity POINT_DATA.Activity activity classification
ACTS Act INTERACT_DATA.Act interaction classification
DATA_STRUCTURES Data_Structure SETUPIDS.Data_Structure version of data structure produced by the palmtops which collect data
CONTEXT_TYPES Context_type MPIS.Context_type context in which a multiparty interaction occurs
FOODCODES Foodcode POINT_DATA.Foodcode name of a food item
FOODTYPES Ftype FOODCODES.Ftype food category
KIDCONTACTS Kidcontact FPOINTS.Kidcontact spatial relationship between mother and infant
MPIACTS Mpiact MPI_DATA.MPIAct multiparty interaction classification
NCODES Ncode NEIGHBORS.Ncode neighbor classification
PALMTOPS Palmtop SAMPLES.Palmtop hand-held computer used in the field
PARTUNKS Unksname MPI_PARTS.Unksname problem in identifying participant in a multiparty interaction
POSTURES Posture POINT_DATA.Posture designated posture
PROGRAMIDS Programid SAMPLES.Programid version of program used on the palmtops to collect data
SETUPIDS Setupid SAMPLES.Setupid setupfile used on the palmtops to collect data
SUCKLES Suckle FPOINTS.Kidsuckle infant suckling activity
 
Sexual Cycles and The Sexual Cycle Day-By-Day Tables
Table Id Column Related Column(s) One entry for every possible choice of...
PCSCOLORS Color SEXSKINS.Color paracallosal skin coloration
 
Darting
Table Id Column Related Column(s) One entry for every possible choice of...
DART_SAMPLE_CATS Ds_cat DART_SAMPLE_CATS.DS_Cat category of darting sample type
DART_SAMPLE_TYPES DS_Type DART_SAMPLE_TYPES.DS_Type type of sample collected during dartings
DRUGS Drug DRUGS.Drug anesthetic drug
LYMPHSTATES Lymphstate DPHYS.Ringnode, DPHYS.Lingnode, DPHYS.Raxnode, DPHYS.Laxnode, DPHYS.Lsubmandnode, DPHYS.Rsubmandnode lymph node condition
PARASITES PARASITE TICKS.Tickkind parasite species, species developmental stage, or kind of parasite sign counted
TCONDITIONS Tcondition TEETH.Tcondition physical condition of a tooth
TICKSTATUSES Tickstatus TICKS.Tickstatus parasite count outcome category
TOOTHCODES Tooth TEETH.Tooth adult or deciduous tooth
TOOTHSITES Toothsite TOOTHCODES.Toothsite dental site within the mouth
TSTATES Tstate TEETH.Tstate tooth presence
 
Inventory
Table Id Column Related Column(s) One entry for every possible ...
INSTITUTIONS Institution LOCATIONS.Institution, NUCACID_LOCAL_IDS.Institution, TISSUE_LOCAL_IDS.Institution possible locale where tissue and nucleic acid samples can be stored or used
MISID_STATUSES Misid_Status TISSUE_DATA.Misid_Status level of confidence in the identity of a tissue sample
NUCACID_CONC_METHODS Conc_Method NUCACID_CONC_DATA.Conc_Method method used for quantifying nucleic acid concentrations
NUCACID_CREATION_METHODS Creation_Method NUCACID_DATA.Creation_Method method used for creating nucleic acid samples
NUCACID_TYPES NucAcid_Type NUCACID_DATA.NucAcid_Type type of nucleic acid sample
STORAGE_MEDIA Storage_Medium TISSUE_DATA.Storage_Medium medium used for storage/archiving of tissue samples
TISSUE_TYPES Tissue_Type TISSUE_DATA.Tissue_Type type of tissue sample
 
SWERB Data (Group-level Geolocation Data)
Table Id Column Related Column(s) One entry for every possible ...
ADCODES ADCode SWERB_LOC_DATA.ADcode relationship between baboon groups and sleeping groves.
SWERB_LOC_CONFIDENCES Conf SWERB_LOC_DATA_CONFIDENCES.Confidence confidence score used when analyzing the accuracy of a recorded observation of a location.
SWERB_LOC_STATUSES Conf SWERB_LOC_DATA.Loc_Status status for a recorded observation of a location.
SWERB_TIME_SOURCES Source SWERB_BES.Bsource, SWERB_BES.Esource data source used to estimate beginning and ending of observation bouts
SWERB_XYSOURCES (SWERB Time Sources) Source SWERB_GW_LOC_DATA.XYSource data source used to obtain XY coordinates
 
Weather Data
Table Id Column Related Column(s) One entry for every possible choice of...
WEATHERHAWK_SOFTWARES WSoftware WEATHERHAWK.WSoftware software used to retrieve data from a WeatherHawk instrument
WSTATIONS Wstation WREADINGS.Wstation meteorological data collection location or device

The Sys_Period Column

Beginning with Babase 5.0, nearly every table in Babase has a column called "Sys_Period", which shows the range of time when the data in a row is considered "valid". When a row in a table in the babase schema is updated or deleted, the "old" version is no longer "valid" and is saved in a corresponding table in the babase_history schema.

Note

All data in the babase schema are valid, simply by virtue of their being in that schema. Users should not let this discussion of validity mislead them into undue suspicion of the accuracy of the data.

Updates to this column should only be performed automatically by the system, when data are inserted, updated, or deleted. Manual updates to this column are only allowed when done by an admin[20].

In the babase schema, the lower bound of the Sys_Period column indicates when the row was last updated, when the row was inserted to the table, or when the Sys_Period column was added to the table, whichever is most recent. The upper bound of the Sys_Period column for tables in that schema will always be NULL, meaning "no end" (yet).

In the babase_history schema, each row represents an old "version" of the row. In these tables, the lower bound of the Sys_Period column is the timestamp of the INSERT or UPDATE that created that version of the row, or the date and time that the Sys_Period column was added to the original table, whichever is most recent. The upper bound is the timestamp of the INSERT, UPDATE, or DELETE that rendered the row no longer "valid".

In all tables, this column is a timestamp range (with time zone), with inclusive lower bound and exclusive upper bound. The lower bound cannot be NULL, and defaults to the current_timestamp when the row is inserted/updated.



[20] Manual updates probably shouldn't be allowed either, but we need to allow automatic updates resulting from legitimate data changes made by babase editors. To allow this, the rule is that only admins are allowed to update this column at all, and the "versioning" function is always run as an admin.


Page generated: 2022-11-02T11:45:50-04:00.