Next / Previous / Contents / NM Tech homepage

4. General design notes

The first step in redesigning the older database is to represent it as an entity-relationship model.


Describes one 15-mile-diameter count circle by the latitude and longitude of its center.


One political region: a U.S. state, a Canadian province, or the only other nation represented in this database, the circle located on the French islands of Saint Pierre and Miquelon.


One country.


A physiographic region stratum code as defined by the U.S. Fish & Wildlife Service's Bird Banding Lab. For the authority file, see Section 2, “Downloadable files”. This code is useful for grouping circles by their biogeographic similarity. Many circle records have not been coded for physiographic strata. Circles may have up to two physiographic strata codes, and for those that have two, the first code is the major stratum and the second the minor stratum, so the ordering of the two codes is important.


This entity describes one year in which there is a published census of the circle.

kind of bird

This entity represents a specific kind of bird seen in one year, and the number of individuals of that kind that the counters saw. Note that there may be many records for a given species within one effort entity, differing by several details: age; sex; whether seen count day or only during count week; or whether the identification is in question.

Note also that on a few occasions an effort has resulted in zero birds (mainly in remote parts of Alaska and Canada), but this is still considered a valid count.

4.1. Attributes of the principal entities

The tables that represent the entities described above will carry the names of those entities, as plurals. Here are the attributes of these tables, and some discussion of how they are derived from the old database.

4.1.1. Attributes of the nations table

For the script that loads this table, see Section 7, “The staticloader script: Populate the static tables”.


Three-character code for the country.


Full name of the country.

4.1.2. Attributes of the regions table

For the script that loads this table, see Section 7, “The staticloader script: Populate the static tables”.


National code for this region, defined in Section 4.1.1, “Attributes of the nations table”.


Two-character postal code, e.g., HI or YT.


Conventional name of the region, e.g., “West Virginia” or “Province Quebec”.

4.1.3. Attributes of the circles table


North latitude of the circle's center in degrees and minutes as ddmm.


West longitude of the circle's center in degrees and minutes as dddmm.


Describes whether salt water occurs in the circle. This attribute is not always properly encoded, so the lack of a code does not imply a lack of salt water. Codes are:

(blank) Unknown or no salt water.
p Pelagic: the entire circle is in open ocean.
o Some open ocean is included in the circle.
e Some ocean estuary is included in the circle, but no open ocean.

Code to indicate an area that is not the standard 15-mile-diameter circle. As with the water attribute, not all circles were properly encoded. Code values may be any of:

(blank) Standard circle or unknown shape.
p Pelagic-only transect.
x Not a pelagic-only transect, and not a standard circle.

The published name of the circle. Many circles have changed their names; generally the attribute in the circles table is the last name used for that particular center. A few standard abbreviations are used:

M.A. Management Area
N.M. National Monument
N.P. National Park
N.W.R. National Wildlife Refuge
P.P. Provincial Park
S.P. State Park
W.M.A. Wildlife Management Area

4.1.4. Attributes of the physios table


Two-digit code for the physiographic stratum, with left zero fill.


Description of this physiographic stratum, e.g., “Southern Rockies”.

4.1.5. Attributes of the cir_reg table

This table represents the many-to-many relation between circles and regions.

lat, lon

Link to the circles table.


Position of this region within the list of regions for the circle. This value is necessary because the regions are ordered. Values are 0 for the first or only region; 1 for the second region; 2 for the third region.


Link to the regions table.

4.1.6. Attributes of the cir_physio table

This table represents the many-to-many relation between circles and physiographic strata.

lat, lon

Link to the circles table.


Position of this stratum code: 0 for the first or only stratum, 1 for the second.

4.1.7. Attributes of the efforts table

Each row of this table represents the censusing of one circle in a given year number.


Latitude, encoded as in Section 4.1.3, “Attributes of the circles table”.


Longitude, encoded as in Section 4.1.3, “Attributes of the circles table”.


The year number, three digits, with left zero fill. For example, “043” for the Forty-third CBC (December 1942 and January 1943).


A five-character key that uniquely identifies an effort within a year. In particular, this field can help a researcher rapidly find the published data in the original periodical. See Section 3.5, “Year key” for a discussion of what actually appeared in the published data.

For year numbers 1–90, this column has format NNNNX, where NNNN is the serial number within the year, and X is either blank or a lowercase letter.

For years 91 through the present, this column's format always has the four-character SSKK form.

See Section 8, “Conversion from the old MySQL database” for more information about the origin of this field.


The date of the count, if known. For many records this is the date of Christmas because the true date was not recorded in the old database.


Published latitude, if known. May be null.


Published longitude, if known. May be null.


Circle name as published. The old database also tracked the region codes as published, but there is no strong reason to retain these data.


Number of observers; an integer, one or greater.



This attribute and all the remaining attributes in this table may be null.

Total party-hours, to tenths.


Party-hours on foot.


Party-hours by car.


Party-hours by means other than foot or car.


Hours (not party-hours) by feeder-watchers.


Hours “owling” or, as it was later known, nocturnal birding.


Total party-miles, to tenths.


Party-miles by means other than foot or car.


Miles owling or other nocturnal birding.

4.1.8. Attributes of the censuses table


Latitude, encoded as in Section 4.1.3, “Attributes of the circles table”.


Longitude, encoded as in Section 4.1.3, “Attributes of the circles table”.


The year number, encoded as in Section 4.1.7, “Attributes of the efforts table”.


Sequence number of this record within the circle and year. Audubon chose to discard this field in their database, but it is vital to the Christmas Bird Count Database corrections project, because the records must be in the original order to be proofread efficiently against the original publication.


First or only form code describing the type of bird. Example: AMEROB for American Robin. These codes are defined in the nomenclature system specification.

(blank) Not a species pair or hybrid.
/ Species pair; the code for the second alternative is in the alt_form attribute.
x Hybrid; the code for the second assumed parent form is in the alt_form attribute.

Second form code when the rel attribute is not blank; null when rel is blank.

Because, for example, “Downy Woodpecker/Hairy Woodpecker” is the same kind of bird as “Hairy Woodpecker/Downy Woodpecker”, the form codes are always ordered such that, lexically, the form code is less than the alt_form code so that a given species pair or hybrid will always have the same representation.


Age code.

(blank) Unknown age class.
a Adult.
i Immature, subadult, or juvenal plumage.
p Female or immature. Yes, female is a sex and not an age class, but we arbitrary place this category under age.

Sex code.

(blank) Sex unknown.
m Male.
f Female.

Count-week indicator (see Section 3.7, “Count week birds”). Normally blank; contains “+” for count-week birds that were not seen count day.


Questionable ID flag. Normally blank; contains “q” if the editor indicated some doubt as to whether this species occurred in the circle at all. Records that are in questions due to abnormally high numbers are not flagged here.


Number of individuals. This is often encoded as “-1” when the number is unknown. Audubon's version of the database uses zero when the number is unknown. For count week birds, this attribute is generally -1, but a few such records contain actual numbers.