From: Greg Stark <gsstark@mit.edu>
To: Alexandre Leclerc <alexandre.leclerc@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Data type to use for primary key
Date: 24 Nov 2004 01:52:52 -0500
Message-ID: <87hdnfwxff.fsf@stark.xeocode.com> (raw)
In-Reply-To: <1dc7f0e30411230829bd1ed@mail.gmail.com>
References: <1dc7f0e30411221232603c4617@mail.gmail.com>
<200411221654.56673.josh@agliodbs.com>
<1dc7f0e30411230829bd1ed@mail.gmail.com>
Alexandre Leclerc <alexandre.leclerc@gmail.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gsstark@mit.edu, alexandre.leclerc@gmail.com
Subject: Re: Data type to use for primary key
In-Reply-To: <87hdnfwxff.fsf@stark.xeocode.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox