On Fri, May 22, 2009 at 9:04 AM, Brandon Metcalf
<brandon@geronimoalloys.com> wrote:
> s == sam@samason.me.uk writes:
>
> s> On Fri, May 22, 2009 at 08:41:46AM -0500, Brandon Metcalf wrote:
> s> > I am looking for criteria on deciding whether or not to use a serial
> s> > (auto-incrementing) key for rows in a table.
>
> s> Wow, that's the second time today someone asked that!
>
> s> > Intuitively, it's pretty clear to me when a serial index is called
> s> > for. Is there a succinct set of guidelines that one could go by?
>
> s> Not that I'm aware of; it's a fuzzy design choice with benefits and
> s> costs for either option. There are lots of people who arbitrarily
> s> pick one side which tends to make things worse, using one or the other
> s> *exclusively* will add complication. General terms to search for are
> s> Natural keys vs. Surrogate keys.
>
> The search terms help. I wasn't searching for the right thing and
> finding very little information.
The periodic table of the elements, state names, etc are all the kind
of data used in what I call lookup tables. They tend to be static,
and are used to ensure that the rest of the database are using the
proper values. In these cases it's almost always best to use the
natural key, and FK to that from another table.
OTOH, if you've got things like customer records, and there are
millions of them, it's often best to use a surrogate key because it's
usually smaller and provides better performance where it counts. A
well designed database will often use both types of keys, because they
solve different problems in terms of performance, durability,
abstraction, etc.