Re: Indexed PF vs. LF performance

Not sure what this has to do with an identity key. You would never use
an identity key for data entry. The only purpose of an identity key is
to provide a unique identifier for each record in a table(A surrogate
key). That identifier is then used in any table that need that
cardinal key. It seems to me that you are going to use surrogate keys
or not. My experience has been that virtually all new databases being
built are going to use surrogate keys. They provide a extremely
powerful tools for solving database problems.

State Table
ID Code Description
1 AL Alabama
2 NV Nevada
3 IL Illinois

City Table
ID Code Description State
1 CHI Chicago 3

Customer Table
ID Code Name Address City
1 SAMSC Sam's Club 134 White St 2



On Tue, Jul 31, 2012 at 4:07 PM, Nathan Andelin <nandelin@xxxxxxxxx> wrote:

In my case, I would make that architecture decision based on

requirements.

Same here. I've been using more sequential, numeric identity keys lately, but Rob had a good point about the State Code table. The state code is a lot more descriptive than a numeric identifier, and easier to remember for data entry, and very unlikely to change.

Consistency is a consideration but not an overriding
one.

I agree.

The trick is deciding when they are appropriate, and that
can be different for different circumstances.

One question I ask myself is whether there might be any cause to allow users to change an otherwise descriptive key field in a maintenance program. Maybe it's a data warehouse used for reporting where rules of normalization may not strictly apply. If so, then use an identity key rather than a descriptive one.

-Nathan.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.