Database Reference
In-Depth Information
see a crow's foot on your ER diagram, you need a foreign key in the table on the crow's foot
side.
NOTE
That rule bears repeating:
When you see a crow's foot on your ER diagram, put a foreign key field in
the table on the crow's foot side
. The foreign key refers to whatever table is on the other end of the
line. This is one of the very few absolute hard-and-fast rules in relational database design, so cling
to this singular bit of simplicity when you start to have doubts.
Choosing a Good Primary Key Field
A primary key is most often a made-up serial number, like the one you made in your Lease
Agreement database. You'll use that same technique a little later in this chapter, when you
tell FileMaker to automatically make up a unique primary key value for each new record.
This kind of primary key, based on purely made-up data, is called a
surrogate key
.
NOTE
Unless you generate the value in your database, it's not a surrogate key, even if somebody else made
it up. A surrogate key is made up
by your database
.
Occasionally, your table has a real value that meets the requirements for a key. For example,
if your Product database has a field for your internal Inventory Control Number, you may be
able to use that field as the primary key. If you use some real piece of data as a key, then it's
called a
natural key
. Surprisingly, though, the vast majority of tables don't have a field that
meets the criteria for a natural primary key. Take, for example, the Phone Number field in a
contact database of people. Phone numbers are
usually
unique, and don't change
all that of-
ten
. But words like “usually” and “often” have no business in a discussion about good key
fields. (See the box below for the reasons why.)
