Re: Table structure

Yes, that is my recommendation. Sorry, I can't comment on the performance impact of a VARCHAR2 index key vs. a NUMBER index key.

"A. G. Mueller" wrote:

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