Next / Previous / Contents / NM Tech homepage

4.2. SQL considerations

Each of the major tables shown in the diagram in Section 4, “General design notes” will become a table in the SQL representation.

Two more tables are required to manage many-to-many relationships.

Here is a revision of our original entity-relationship model showing the final tables and their relations. Primary key columns are indicated with an asterisk “*”, and the arrows show the foreign key relations.

For the censuses table we have a choice of two unique keys. The concatenation of lat + lon + year_no + year_key + seq_no is the unique primary key.

Note

It would be nice if every circle were counted exactly once in a given year number, but there are hundreds of exceptions. Audubon stipulated starting in the mid-1930s that counts within one year number should not overlap, but exceptions persisted until the 55th year. This is why the year_key must be part of the primary key.

We'll need another index to search for kinds of birds. Within a given circle-year, we must concatenate seven columns to insure uniqueness: form + rel + alt_form + age + sex + plus + q. So the secondary index will include these seven fields, plus lat + lon + year_no + year_key + seq_no.