Identity Attribute or not?

Say you've got a nice and simple 1-to-Many relationship between the following tables:

TABLE USERS
username_id(PK) username salary

1                             Jon             10
2                             Mary            5
3                             Pete            10

TABLE AUDIT
id(PK)               username_id(FK)            creation_date

1                               1                                  12:15
2                               1                                  15:04
3                               2                                  10:45

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

TABLE USERS

username(PK)                     salary

Jon                                       10
Mary                                      5
Pete                                      10

TABLE AUDIT
id(PK)          username(FK)             creation_date

1                     Jon                            12:15
2                     Jon                            15:04
3                     Pete                           10:45

This now simplifies my SQL when inserting a new record into the AUDIT table. I no longer need to lookup a username_id given a username (and handle errors). I can just enter
the username into the AUDIT table and have the database verify that the username actually exists in the USERS table.

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

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

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

TIA Received on Tue Sep 24 2002 - 09:02:25 CEST