From: Craig James <craig_james@emolecules.com>
To: valiouk@yahoo.co.uk
Cc: Jay <arrival123@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Using PK value as a String
Date: Mon, 11 Aug 2008 09:03:58 -0700
Message-ID: <48A062EE.5050408@emolecules.com> (raw)
In-Reply-To: <18986.60888.qm@web25807.mail.ukl.yahoo.com>
References: <18986.60888.qm@web25807.mail.ukl.yahoo.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: craig_james@emolecules.com, valiouk@yahoo.co.uk, arrival123@gmail.com
Subject: Re: Using PK value as a String
In-Reply-To: <48A062EE.5050408@emolecules.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox