Re: Table structure

Herr Mueller,

Here are a few points to think about:

1. a logon ID is a point of access to a computer system and is not necessarily 1-to-1 with the people who own them. If warranted, you might want to have one table for the 'person' entity and another for the 'user' entity to allow one person to own more than one logon ID e.g. a production support rep might have one for her job with high access privilege and another for her personal needs (e.g. entering expenses) with normal privilege. This scheme means you can even reassign a logon from one individual to another if necessary (e.g. PRODSUPREP). Although I am generally against such 'generic' logon IDs - better that each logon ID only has one owner in its life, for auditability.
2. If the Logon ID is the primary key, it is updateable but you'll run into constraint violations if there are any child tables that refer to the logon ID as the foreign key and you want to update the primary key. If you think it will never need updating, think again. There will always be logons created that misspell a user's name, and that user will move mountains to get the mistake corrected. Also, the naming convention may have to change for security reasons or post-merger.
3. The argument of 'natural' keys vs. generated keys has been raging for decades. My opinion: only use generated keys for primary/foreign key relationships. If an attribute or combination of attributes is unique, then by all means place a unique constraint on those attributes. The nice thing about a generated key is that EVERYTHING about a record may change, but your artificial numeric identifier is safe from all change and thus you never need to deal with cascading updates or foreign key violations or deleting/re-inserting rows simply because an attribute value needs to change.

hth
Martin Doherty

"A. G. Mueller" wrote:

> Hello,
>
> I want to create a user table to store user information, e.g. First name,
> Last name, Logon ID (a uniquely generated ID for each user), Phone, etc.
>
> Is there anything wrong with creating the primary key on the Logon ID? The
> Logon ID will be a VARCHAR2 of eight long.
>
> I'm not sure if creating the primary key index on a string is a good idea.
> From what I've seen, it's standard to create a "USER_ID" column, of
> NUMBER(6) which is a uniquely generated number (from a sequence), leaving
> the Login ID as is.
>
> Any ideas?
>
> -== A.G. Mueller ==-