X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 292B19FA66C for ; Fri, 7 Jul 2006 05:32:41 -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 27660-06 for ; Fri, 7 Jul 2006 05:32:36 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.187]) by postgresql.org (Postfix) with ESMTP id 833529FA65C for ; Fri, 7 Jul 2006 05:32:36 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id l35so175986nfa for ; Fri, 07 Jul 2006 01:32:34 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=Q7ufmjPym66f5keyDxi8/DDwthjT84RerDRBiFTmcRIgSoCtMj4dEtqvguMftIASvBnDNzTn3qiCzH8TDiMCB26G+h6NTolmf5MYowoZD9r6/bnKSxlW0L0LdZhW2QPiLD+MtdAp4neCSvVId7+93KZ6dW+qKdZAduIPW3sA89g= Received: by 10.78.140.17 with SMTP id n17mr568072hud; Fri, 07 Jul 2006 01:32:34 -0700 (PDT) Received: by 10.78.29.2 with HTTP; Fri, 7 Jul 2006 01:32:33 -0700 (PDT) Message-ID: Date: Fri, 7 Jul 2006 03:32:33 -0500 From: "Aaron Bono" To: "David Clarke" Subject: Re: Alternative to serial primary key Cc: "Scott Marlowe" , "operationsengineer1@yahoo.com" , pgsql-sql@postgresql.org In-Reply-To: <12b7ac1e0607070107t78a808aeuc8aba982d593abff@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_106851_5501035.1152261153945" References: <20060706183920.46008.qmail@web33314.mail.mud.yahoo.com> <12b7ac1e0607061330o34adbd6ds9bd624d3889c7fe7@mail.gmail.com> <1152222607.13851.280.camel@state.g2switchworks.com> <12b7ac1e0607070107t78a808aeuc8aba982d593abff@mail.gmail.com> X-Google-Sender-Auth: c827f6e994138ca8 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/83 X-Sequence-Number: 25657 ------=_Part_106851_5501035.1152261153945 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 7/7/06, David Clarke wrote: > > > The question remains regarding the use of a string value as a primary > key for the table and as a foreign key in other tables. If I use the > address column as a foreign key in a differrent table will postgres > physically duplicate the data or will it simply attach the appropriate > index magically behind the scenes? I agree that the address column is > fairly small and I've heard of others using the likes of GUIDs as key > values which are not a lot smaller than I would expect my average > address to be. Theoretically using the address as a foreign key WILL duplicate the data. I don't know if there is some fancy way PostgreSQL optimizes the use of space for indexed fields or foreign keys though. Might need to get feedback from someone who has looked at the source code for that and it may depend on the version you are using. Unless you are looking at a huge number of records though, the size difference is of little concern - disk space is cheap. As far as should you use varchar as a foreign key - as someone mentioned before, you may be treading on religious territory there. It can be done and is done frequently. I never do it but I really cannot come up with a compelling argument why not other than it is just one of the standards I have adopted for my team. -Aaron ------=_Part_106851_5501035.1152261153945 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 7/7/06, David Clarke <pigwin32@gmail.com> wrote:

The question remains regarding the use of a string value as a primary
key for the table and as a foreign key in other tables. If I use the
address column as a foreign key in a differrent table will postgres
physically duplicate the data or will it simply attach the appropriate
index magically behind the scenes? I agree that the address column is
fairly small and I've heard of others using the likes of GUIDs as key
values which are not a lot smaller than I would expect my average
address to be.

Theoretically using the address as a foreign key WILL duplicate the data.  I don't know if there is some fancy way PostgreSQL optimizes the use of space for indexed fields or foreign keys though.  Might need to get feedback from someone who has looked at the source code for that and it may depend on the version you are using.  Unless you are looking at a huge number of records though, the size difference is of little concern - disk space is cheap.

As far as should you use varchar as a foreign key - as someone mentioned before, you may be treading on religious territory there.  It can be done and is done frequently.  I never do it but I really cannot come up with a compelling argument why not other than it is just one of the standards I have adopted for my team.

-Aaron
------=_Part_106851_5501035.1152261153945--