From: Chris Browne <cbbrowne@acm.org>
To: pgsql-sql@postgresql.org
Subject: Re: Table design question
Date: Thu, 01 Jun 2006 15:56:00 -0400
Message-ID: <60r728sl9r.fsf@dba2.int.libertyrms.com> (raw)
References: <12b7ac1e0606010405u6e062f71mf4adbaeb6c46df5f@mail.gmail.com>
<e5n90p$1i7g$1@news.hub.org>
"codeWarrior" <gpatnude@hotmail.com> writes:
> I never use anything other than "id SERIAL NOT NULL PRIMARY KEY" for my
> PKEY's -- as an absolute rule -- I guess I am a purist... Everything else
> (the other columns) can have unique constraints, etcetera and be FOREIGN
> KEYS, etc...
>
> Try INSERTING your 100 character "natural" key into a table with 10M++ rows
> only to find out there there is already a duplicate.... talk about a
> performance hit.... or SELECT -- you end up using way too much RAM and
> bandwidth -- unecessarily...
Celko is decidedly *NOT* promoting the notion that you should use a
100 byte long "natural key."
Jamie's comments of "Orthodox versus Reform" seem reasonably
appropriate in outlining something of the difference between the
positions.
Just because Celko is "Orthodox" doesn't mean he's *stupid*; he makes
all sorts of noises about using some ISO standard (11179) such that
your primary key values are actually described in some authoritative
manner.
<http://metadata-standards.org/11179/;
If you go about using ISO-11179, then, in principle, you could
register your scheme for describing IDs for the objects in your system
with some ISO people so that others might even become aware of the
official status of this.
I may not care for doing this; you may not either; a company that
builds auto parts that they want to sell into the automotive industry
may care about standardizing their part IDs quite a lot.
They're not interested in generating stupidly long identifiers; that's
inefficient in many ways...
> IMHO: You ought to use a numeric, auto-generated sequence (SERIAL)
> for you PKEY's ...
That's definitely the "Reform" position ;-), and it is a position that
can be rationally held.
--
select 'cbbrowne' || '@' || 'cbbrowne.com';
http://cbbrowne.com/info/multiplexor.html
Rules of the Evil Overlord #185. "If I capture an enemy known for
escaping via ingenious and fantastic little gadgets, I will order a
full cavity search and confiscate all personal items before throwing
him in my dungeon." <http://www.eviloverlord.com/;
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-sql@postgresql.org
Cc: cbbrowne@acm.org
Subject: Re: Table design question
In-Reply-To: <60r728sl9r.fsf@dba2.int.libertyrms.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