Re: Table design question

"codeWarrior" <gpatnude(at)hotmail(dot)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/>