Database Reference
In-Depth Information
WORD TO THE WISE: GOING NATURAL
Should I try to find natural keys for the tables in my database if I can?
This question has generated an eternal debate in the broader database world. Some ivory-tower the-
orists are convinced that natural keys are superior to surrogate keys for two primary reasons. First,
they're
meaningful
: When you look at a natural key in your own database, it means something to
you. Second, if a key is also real honest-to-goodness meaningful data, then, in a relational database
situation, your table always has at least one piece of good information from the table it relates to. If
that happens to be the snippet you need, you save the software the trouble of going to another table
and finding the right record. Thus, the theory goes, natural keys make database programs run a trifle
faster.
But for the kinds of databases you're likely to build with FileMaker, neither of these concerns comes
up very often. If a surrogate key isn't all that meaningful to the database user, just don't put it on the
layout. It's perfectly normal to have utilitarian keys that users never see. And as for performance,
the minuscule increase in speed is almost never significant enough to matter.
And there's a much more significant argument against trying to find an acceptable natural key; It's
usually impossible. You almost never have a normal piece of data in a record that meets all the cri-
teria for a good primary key.
Even natural keys that really seem like great choices often turn out to be problematic. Suppose you
work for a company that assigns an employee ID to each employee. You're building a database to
keep track of employee stock options. Just like a Social Security Number, Employee ID is a surrog-
ate key to somebody, but it's a natural key to you. You decide to make it your primary key. Then you
discover you need to track stock options for employees even if they quit and then return to the com-
pany. When they do this, their employee IDs change, and your database can't track them properly
without some inconvenient upkeep. If you had used your own surrogate key instead, you wouldn't
have this problem.
The penalty for a bad key choice can be huge: anything from lost connections in your data to the
need for a major overhaul of the system. By contrast, surrogate keys are easy and always work.
Once you accept the fact that your database will have an extra field that serves no other purpose
than to be the primary key, the choice becomes a no-brainer. Don't bother with natural keys.
In fact, in most cases, the only fields that meet the requirements for a natural key are, in real-
ity, surrogate keys from somebody else's database. Your database may contain employee
numbers that come from your company's payroll system, part numbers from your supplier's
catalog, or document numbers from your corporate knowledge base. All these numbers are
surrogate keys in some system somewhere, but they may be natural keys to you.
