X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id C5ED19FA5C8 for ; Thu, 1 Jun 2006 14:44:00 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 39745-03 for ; Thu, 1 Jun 2006 14:43:57 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id E02AE9FA040 for ; Thu, 1 Jun 2006 14:43:56 -0300 (ADT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id BE1DF31DC3; Thu, 1 Jun 2006 19:43:55 +0200 (MET DST) From: "codeWarrior" X-Newsgroups: pgsql.sql Subject: Re: Table design question Date: Thu, 1 Jun 2006 10:45:49 -0700 Organization: iDynaTECH, Inc. Lines: 43 Message-ID: References: <12b7ac1e0606010405u6e062f71mf4adbaeb6c46df5f@mail.gmail.com> Reply-To: "codeWarrior" X-Complaints-To: usenet@news.hub.org X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2900.2869 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869 X-RFC2646: Format=Flowed; Response To: pgsql-sql@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/16 X-Sequence-Number: 25252 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"" 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 >