Re: Table design question

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
>