8. Conversion from the old MySQL database

The previous database schema has been unchanged since 1998. This database currently exists as a MySQL database, and will provide the initial values for the current (Postgresql) version.

The initial setup of the Postgresql database proceeds in these steps.

The script described in Section 7, “The staticloader script: Populate the static tables” drops any existing tables, recreates the database, and then populates the nations and regions tables.
Section 9, “transloader: Copy over the MySQL database” describes the script that populates the rest of the tables from the MySQL database.

8.1. Schema of the 1998 database

The old MySQL database had a very different structure, and was poorly normalized. In particular, there was an unnecessary level of relation in the stnd table, which mapped a key called count ID to a circle, and three other tables used the count ID to link to the stnd table, rather than directly to the circle table. The count ID is a composite of two different fields, and has two different formats depending on the route the data took to get into the database originally:

Count year	Count ID format
`001`–`090`	`YYYNNNNX`
`091`–present	`YYYSSKK`

The YYY portion is the count year, with left zero fill. The rest of this field is the “year key” discussed in Section 3.5, “Year key”.

The part of this key after the YYY part is preserved in the new database, in the year_key column of the efforts table.

Here, then, is an entity-relationship model for the old database. The count ID is used as the key for all the relations shown here, except that the relation from stnd to cir uses the lat_lon column.

Note the two one-to-one relationships. There is one stnd row for each circle-year, and there is no compelling reason to distribute the attributes of a circle-year over two other tables (aspub and eff). So, converting the old schema to the new will lump the old stnd, aspub, and eff tables into the new efforts table.

Refer to the 1998 database specification for a general description of the older schema. Here is a table showing the actual MySQL column names and types.

Table name	Column name	Column type
`cir`	`lat_lon`	`CHAR(9)`
	`physio`	`VARCHAR(4)`
	`water`	`CHAR(1)`
	`odd`	`CHAR(1)`
	`regions`	`VARCHAR(6)`
	`name`	`VARCHAR(80)`
`stnd`	`lat_lon`	`CHAR(9)`
`stnd`	`count_id`	`CHAR(8)`
`aspub`	`count_id`	`CHAR(8)`
	`as_lat_lon`	`CHAR(9)`
	`as_regions`	`VARCHAR(6)`
	`as_name`	`VARCHAR(80)`
`eff`	`count_id`	`CHAR(8)`
	`yyyymmdd`	`CHAR(8)`
	`n_obs`	`INT`
	`ph_tot`	`DECIMAL(5,1)`
	`ph_foot`	`DECIMAL(5,1)`
	`ph_car`	`DECIMAL(5,1)`
	`ph_o`	`DECIMAL(5,1)`
	`h_fd`	`DECIMAL(5,1)`
	`h_owl`	`DECIMAL(5,1)`
	`pm_tot`	`DECIMAL(5,1)`
	`pm_f`	`DECIMAL(5,1)`
	`pm_c`	`DECIMAL(5,1)`
	`pm_o`	`DECIMAL(5,1)`
	`m_owl`	`DECIMAL(5,1)`
`cen`	`count_id`	`CHAR(8)`
	`seq_no`	`CHAR(3)`
	`form`	`CHAR(6)`
	`rel`	`CHAR(1)`
	`alt_form`	`CHAR(6)`
	`age`	`CHAR(1)`
	`sex`	`CHAR(1)`
	`plus`	`CHAR(1)`
	`q`	`CHAR(1)`
	`census`	`INT`

pycbc: A Python interface to the Christmas Bird Count database

8. Conversion from the old MySQL database

8.1. Schema of the 1998 database