Changes to Babase between 1.0 and 2.0

A number of changes were made to Babase in the transition from FoxPro (Babase 1.0) to PostgreSQL (Babase 2.0). This appendix attempts to document changes made to data semantics and, on occasion, data values.

By far the most significant change is that the database itself now performs most data validation. A large number of data validation rules were introduced along with this change.

The 2.0 release of Babase also adds documentation where there was none and includes redesign of some Babase components which were added to Babase 1.0 late in its life. Notable is the entire point sample portion of the database, which was not documented in Babase 1.0 and was redesigned for Babase 2.0. The REPSTATS, CYCSTATS, CYCGAPS and related tables are also new to Babase 2.0 as their 1.0 implementations were never completed or documented. Interpolation was also redesigned and extensively documented.


GROUPS.Permanent column becomes a date

In Babase 1.0 the GROUPS.Permanent was a boolean (y or n). In Babase 2.0 it changed to a date, or NULL if the group never became a permanent group.

GROUPS.Status column removed

The GROUPS.Status column was dropped in Babase 2.0. It was originally intended as a way to mark groups which are no longer censused or which ceased to exist for one reason or another because a group split or group coding changed (particularly with respect to the unknown and suchlike groups used at various times). The functionality of this column is more or less subsumed by the GROUPS.Cease_To_Exist column or obviated by the extensive data cleanup which occurred during the transition of Babase 1.0 to Babase 2.0.

The Statdate is now constrained, when the individual is alive, to be the most recent date on which a census located an individual in a group. Although this was true in practice, the 1.0 system did not require it.

This constraint leads directly to another, when the individual is alive and there are no (non-absent) censuses then the individual's Statdate must be the individual's birth date. Because arbitrary Statdates are not allowed, we prevent automatic changes from erasing manually set Statdates.


The MATUREDATES, RANKDATES, CONSORTDATES, and DISPERSEDATES tables of Babase 2.0 were columns in the Babase 1.0 BIOGRAPH table. Rather than allow NULL data values, in Babase 2.0 entire rows are simply not present when there is no data.

The interpolation procedure changed somewhat. As the interpolation is what creates the MEMBERS table this appendix also describes the changes made to MEMBERS between 1.0 and 2.0.

  • Individuals have a row in MEMBERS for every day of their lives.[320]

    Interpolation now places individuals in the unknown group when individuals' locations cannot be otherwise assigned, for example outside of the 14 day interpolation limit. Formerly, when the individual could not be place in a group on a particular day the individual had no row in MEMBERS on that day.

  • Individuals are no longer always placed in a group, the group in which they were last censused, on their Statdate and this location no longer interpolates.

    When first written, the interpolation procedure was designed to work with females, who are unlikely to be absent from their group for more than 28 days. (Twice the 14 day interpolation limit.) By placing an individual in a group on their Statdate, the group in which they were last censused, the females were assured a row in MEMBERS for every day of their lives. Further, analysis was simplified as each of these rows associated the females with their group (even though at the end of their lives they may not have been present in the group.)

    The new interpolation procedure does not consider the Statdate in its determination of the individual's group membership on that day, although, as always, when the Statdate is a death date it does stop interpolation.

  • There is a change in what happens when an individual is censused absent on his birth day. In the new system, if the individual is censused absent on his birth interpolation will override the absence and place the individual in his Matgrp group in MEMBERS.

    In the old system, if the individual is censused absent on his birth interpolation will not override the absence and place the individual in a group in MEMBERS. As the individual is expected to be somewhere on his birth, it's expected that there be a demography note made for the individual on that date to give the individual a location ' a row in MEMBERS.

  • MEMBERS.Interp may now be NULL. The FoxPro system did not have NULL values. In the new system Interp is NULL when interpolation does not know where the nearest locating census is. See Pre-Analyzed Data Disturbs Interpolation

  • The behavior of interpolation on the last census is now documented.

    The interpolation procedure changed during the period of use of Babase 1.0, but the changes were not documented. The primary change was that interpolation was altered so that it did not interpolate if there was no subsequent, absent or not, censuses. This prevented (almost) every living individual currently monitored from having a 14 day tail of interpolated values following the last entered census -- a tail that would disappear the next time the census information was updated.

The Sexual Cycle Information

The structure of the sexual cycle portion of the database was changed. The CYCLES table became CYCPOINTS and the system became responsible for linking together Mdates, Tdates, and Ddates into CYCLES and computing Seq and Series. The SEXSKINS are automatically associated with cycles. The system also computes automatic Mdates. CYCPOINTS.Source was added to allow tracking of automatically added and estimated data. With the addition of CYCPOINTS the PREGS table links directly to Ddates for conception date and Tdates for resumption dates. PREGS.Resume is automatically calculated unless there is a gap in observation. The CYCGAPS and CYCGAPDAYS tables were added. And the CYCSTATS and REPSTATS tables were modified and made useful.

The PCSKINS table was added to Babase 2.0.

For further information please compare the old and new documentation.


The Babase 1.0 JPSAMPS and FPSAMPS were merged and become POINTS and FPOINTS in Babase 2.0. Along with this change all the support tables used by POINT_DATA and FPOINTS were created.

The Datetime columns on JPSAMPS, FPSAMPS, and ALLMISCS were dropped as they become redundant due to the changes in time representation. (See Time Representation below.)

See The All-Occurrences Focal Point Data below for more changes regarding the time data values.

Time Representation

Babase 1.0 represented times as strings of 5 characters, the 3rd character being a colon and the rest numbers. This was due to the lack of a time data type when the system was first implemented.

Babase 2.0 represents all time values as times, generally using a data type having a precision of 1 second. This facilitates the use of the standard library of time and time interval manipulation operators.

The All-Occurrences Focal Point Data

Psion Palmtop Time Representation

Babase 2.0 simplifies its representation of the times collected using the Psion palmtops by dropping the Datetime column in the tables where it was used. The changes affect the POINT_DATA, ALLMISCS, and INTERACT_DATA tables.

When Babase 1.0 loaded Psion palmtop time data into the FPSAMPS (now POINT_DATA), JPSAMPS (now also POINT_DATA), ADLIBS (now ALLMISCS), and INTERACT_DATA tables it truncated the seconds in the time columns (POINT_DATA.Ptime, ALLMISCS.Atime, Start, and Stop), but retained the seconds in the Datetime column. This means that the time columns of Babase 1.0 recorded a time value up to 59.999 seconds earlier than the Datetime column, the actual time recorded on the Psion palmtop. The program which converts the data from Babase 1.0 to Babase 2.0 uses the Datetime column value, so the new system records the actual Psion palmtop time in its time columns, which now contain a different value than the Babase 1.0 time columns. Babase 2.0 time values are up to 59 seconds later than Babase 1.0's time values.

Views transform time related data

Views are used to transform time related data on POINT_DATA and INTERACT_DATA. Dates are available in Julian format and times are available as seconds past midnight. If necessary the views can be extended to truncate the seconds if there is an ongoing reason to produce time values that are compatible with Babase 1.0.

Support Tables

Name Changes

The names of some support tables were changed between Babase 1.0 and 2.0.

New And Old Support Table Names
Old Name New Name

Additional Support Tables

The following support tables were added in Babase 2.0:

The Addition of Views

Babase 2.0 uses views to abstract the underlying tables. So long as Babase's users use the views rather than the underlying tables, the underlying structure of the database may be changed without affecting the users.

[320] Birth to Statdate, inclusive of both and perhaps a bit beyond Statdate.

Page generated: 2023-11-28T17:36:15-05:00.