Re: Data type to use for primary key

Alexandre Leclerc <alexandre(dot)leclerc(at)gmail(dot)com> writes:

> Thanks for those tips. I'll print and keep them. So in my case, the
> product_code being varchar(24) is:
> 4 bytes + string size (so possibly up to 24) = possible 28 bytes. I
> did the good thing using a serial. For my shorter keys (4 bytes + up
> to 6 char) I will use the natural key.

Realize that space usage is really only part of the issue.

If you ever have two records with the same natural key or a record whose
natural key has changed you'll be in for a world of hurt if you use the
natural key as the primary key in your database.

Basically I never use natural keys except when they're arbitrarily chosen
values defined by the application itself.

Situations where I've used varchars instead of integer keys are things like:

. Individual privileges grantable in a security system.
(things like "VIEWUSER" "EDITUSER" privileges)

. Reference tables for one letter codes used to indicate the type of object
represented by the record.

Actually I see one interesting exception to my policy in my current database
schema. And I don't think I would do this one differently given the choice
either. The primary key of the postal code table is the postal code. (postal
codes are up here in the great white north like zip codes down there.)

This could hurt if they ever reuse an old previously retired postal code,
which isn't an entirely impossible case. As far as I know it hasn't happened
yet though. And it's just so much more convenient having the postal code handy
instead of having to join against another table to look it up.

--
greg