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
Section 9, “
transloader: Copy over the MySQL
database” describes the script that
populates the rest of the tables from the MySQL database.
The old MySQL database had
a very different structure, and was poorly normalized. In
particular, there was an unnecessary level of relation in
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|
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
part is preserved
in the new database, in the
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
cir uses the
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 (
eff). So, converting the old schema to the
new will lump the old
eff tables into the new
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|