From: codeWarrior <gpatnude@hotmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Table design question
Date: Thu, 1 Jun 2006 10:45:49 -0700
Message-ID: <e5n90p$1i7g$1@news.hub.org> (raw)
References: <12b7ac1e0606010405u6e062f71mf4adbaeb6c46df5f@mail.gmail.com>
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...
IMHO: You ought to use a numeric, auto-generated sequence (SERIAL) for you
PKEY's ...
""David Clarke"" <pigwin32@gmail.com> wrote in message
news:12b7ac1e0606010405u6e062f71mf4adbaeb6c46df5f@mail.gmail.com...
> I'm reading Joe Celko's book SQL Programming Style for the second time
> and although I've been an OO developer for quite a few years I'm
> fairly green wrt SQL. Joe is obviously something of a curmudgeon and I
> would fall squarely into his newbie OO developer ordinal scale and I'm
> trying to avoid the slide into stupid newbie OO developer.
>
> So I'm designing a table and I'm looking for an appropriate key. The
> natural key is a string from a few characters up to a maximum of
> perhaps 100. Joe gets quite fierce about avoiding the use of a serial
> id column as a key. The string is unique in the table and fits the
> criteria for a key. So should I follow Joe's advice and use my natural
> key as the primary key? It sounds reasonable but it will mean at least
> one other table will have the string as a foreign key. My postgres
> intro book has id columns all over the place but is it really that big
> an issue these days to have a 100 character primary key? Are there
> postgres-specific implications for either approach?
>
> Thanks
> Dave
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
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: gpatnude@hotmail.com
Subject: Re: Table design question
In-Reply-To: <e5n90p$1i7g$1@news.hub.org>
* 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