Re: Identity Attribute or not?

From: Arto Viitanen <av_at_cs.uta.fi>
Date: 25 Sep 2002 10:24:48 +0300
Message-ID: <we1y7imszj.fsf_at_siwenna.cs.uta.fi>

>>>>> "peter" == peter surma <Peter> writes:

 peter> Hi All, Say you've got a nice and simple 1-to-Many relationship  peter> between the following tables:

 peter> Seeing as I want the username attribute in the users table to be
 peter> UNIQUE and NOT NULL, it qualifies as a primary key, this removes the
 peter> need for the username_id column. The tables now look like this:

 
 peter> This now simplifies my SQL when inserting a new record into the AUDIT
 peter> table.  I no longer need to lookup a username_id given a username
 peter> (and handle errors). I can just enter the username into the AUDIT

 peter> table and have the database verify that the username actually exists  peter> in the USERS table.

 peter> Does MS-SQL 2000 duplicate the username value in both tables? or is
 peter> it smart enough to save space in the AUDIT table and use some kind of
 peter> lookup reference to the USERS table? What about performance?

I guess it is up to you to generate the lookup, namely index.

 peter> I realize that if I change a username, I must cascade updates to all  peter> related tables, but this will hardly ever happen.

 peter> Do you know of any advantages/disadvantages over the two approaches?

Like you say: if you need to change key value, you have to change it everywhere. To me key is something that is permanent, so some kind of ID is better _in this case_.

-- 
Arto V. Viitanen				                  av_at_cs.uta.fi
University of Tampere, Department of Computer and Information Sciences
Tampere, Finland				      http://www.cs.uta.fi/~av/

Received on Wed Sep 25 2002 - 09:24:48 CEST