Re: Table structure

> 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.

Good point, although on this system, every user will be allocated only one ID, not two or three or more. Every user is allocated their own 8-digit ID which so far as I can see, doesn't change. In addition, they will not re-assign login IDs. The same logon (login) ID is used corporate wide.

> 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.

Yes, it's for this reason I thought there has to be a unique ID column for each row, purely for the reason of being referenced from another table. In any case, isn't it a detriment to index a string column type? Performance wise, maybe?

> 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.

So in other words, use a uniquely generated ID (e.g. <MYSEQ-NAME>.NEXTVAL) for the primary key column and keep the login ID (string of 8 characters) as a seperate column?

-== A.G. Mueller ==- Received on Mon Sep 30 2002 - 19:45:37 CDT