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.
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 |
| RESIDENCIES | bout of each individual's residency |
| 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 | focal 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 |
| VAGINAL_PHS | vaginal pH measurement made of a darted individual |
| Analyzed: Darting | |
| Table | One row for each |
| FLOW_CYTOMETRY | flow cytometric analysis of a blood sample collected during a darting |
| 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 |
| TISSUE_SOURCES | Tissue sample that has another tissue sample as its source |
| 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 |
| DIGITAL_WEATHER | digital weather reading reported from an electronic weather collection device |
| WREADINGS | manually collected meteorological data collection event |
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.
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 data collection devices |
| 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 |
| 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 each program used on the devices to collect focal sampling data |
| SAMPLES_COLLECTION_SYSTEMS | Collection_System | SAMPLES.Collection_System | device or "system" used in the field for collecting focal sampling data |
| SETUPIDS | Setupid | SAMPLES.Setupid | setupfile used on the devices to collect focal sampling data |
| STYPES | SType | SAMPLES.SType | protocol for focal sampling data collection |
| STYPES_ACTIVITIES | SType-Activity pair | SAMPLES.SType, ACTIVITIES.Activity | activity classification allowed to be used in each focal sampling protocol |
| STYPES_NCODES | SType-Ncode pair | SAMPLES.SType, NCODES.Ncode | neighbor classification allowed to be used in each focal sampling protocol |
| STYPES_POSTURES | SType-Posture pair | SAMPLES.SType, POSTURES.Posture | posture classification allowed to be used in each focal sampling protocol |
| 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... |
| WEATHER_SOFTWARES | WSoftware | DIGITAL_WEATHER.WSoftware | software used to retrieve data from an electronic weather collection instrument |
| WSTATIONS | Wstation | WREADINGS.Wstation | meteorological data collection location or device |
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.
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.