Database Design

Most of us are capable of designing a robust database that serves most of our needs by considering a few basic points.

1. How will you design your computer database to hold your data?
2. How will you use the data?

A good place to start is ask two basic questions:

1. What was the "thing" about which I recorded information? Was it a bird, a sample of blood, an egg, a seabird colony, a person, a habitat, a plot, or an event? "Things" are called "entities".
2. What information did I record about each thing? Information about "entities" are called "attributes".

Example 1.

Let's assume that you are running a bird-banding station at which you trap birds, band them, and measure their body mass. The "thing" or "entity" about which you are recording information is each individual bird you catch, and the "attributes" recorded for each thing are band number, species, location, and body mass. Traditionally we might record such information on paper in tabular format, with each bird representing a row in the table, and each item of information about each bird recorded in a column, as follows:

This is called a rectangular table and could be stored as a single file in a database management system. In a rectangular table each row is called a "record" and each column is called a "field". Records can be thought of as sampling units or replicants and fields as variables.

Although the rectangular format is usually the most intuitive, it might not be the most appropriate.

Example 2.

Imagine that you have taken 100 water samples in each of two lakes A and B and measured pH and water depth of each sample. You may record the data in your field book as follows:

Although this arrangement might be suitable for a field note book, it is not appropriate for a computer database file. First, the variables are not unique (ie. pH is recorded in two fields), and second, the sampling unit (ie. what the data in each record or row of the data set represents) is unclear. Data arranged in this way would make analysis difficult or impossible without reformatting. For example, you could not produce a graph of depth on the x-axis and pH on the y-axis for both lakes unless you somehow overlaid data from both lakes.

Instead, use the following structure for storage of this data-set in a computer database:

In this format, each variable is stored in a single field and it is clear what a row in the data-set represents one water sample. Note that it is necessary to add a variable to code for the lake from which the sample was taken. This was implicit in the fields names in the first attempt above.

Example 3.