[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-performance
Subject:    Re: [PERFORM] Using PK value as a String
From:       Craig James <craig_james () emolecules ! com>
Date:       2008-08-11 16:03:58
Message-ID: 48A062EE.5050408 () emolecules ! com
[Download RAW message or body]

Valentin Bogdanov wrote:
> --- On Mon, 11/8/08, Gregory Stark <stark@enterprisedb.com> wrote:
> 
> > From: Gregory Stark <stark@enterprisedb.com>
> > Subject: Re: [PERFORM] Using PK value as a String
> > To: "Jay" <arrival123@gmail.com>
> > Cc: pgsql-performance@postgresql.org
> > Date: Monday, 11 August, 2008, 10:30 AM
> > "Jay" <arrival123@gmail.com> writes:
> > 
> > > I have a table named table_Users:
> > > 
> > > CREATE TABLE table_Users (
> > > UserID       character(40)  NOT NULL default
> > '',
> > > Username   varchar(256)  NOT NULL default
> > '',
> > > Email          varchar(256) NOT NULL default
> > ''
> > > etc...
> > > );
> > > 
...
> > But the real question here is what's the better design.
> > If you use Username
> > you'll be cursing if you ever want to provide a
> > facility to allow people to
> > change their usernames. You may not want such a facility
> > now but one day...
> > 
> 
> I don't understand Gregory's suggestion about the design. I thought
> using natural primary keys as opposed to surrogate ones is a better
> design strategy, even when it comes to performance considerations
> and even more so if there are complex relationships within the database.

No, exactly the opposite.  Data about users (such as name, email address, etc.) are \
rarely a good choice as a foreign key, and shouldn't be considered "keys" in most \
circumstances.  As Gregory points out, you're spreading the user's name across the \
database, effectively denormalizing it.

Instead, you should have a user record, with an arbitrary key, an integer or OID, \
that you use as the foreign key for all other tables.  That way, when the username \
changes, only one table will be affected.  And it's much more efficient to use an \
integer as the key than a long string.

Craig

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[prev in list] [next in list] [prev in thread] [next in thread]