Valentin Bogdanov wrote:
--- On Mon, 11/8/08, Gregory Stark <[EMAIL PROTECTED]> wrote:
From: Gregory Stark <[EMAIL PROTECTED]>
Subject: Re: [PERFORM] Using PK value as a String
To: "Jay" <[EMAIL PROTECTED]>
Cc: pgsql-performance@postgresql.org
Date: Monday, 11 August, 2008, 10:30 AM
"Jay" <[EMAIL PROTECTED]> 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
