Re: question ???

"Norman Dunbar" <Norman.Dunbar_at_lfs.co.uk> wrote in message news:A43AA78C3F9DD511AAB100805FBE740D45507F_at_lnewton.leeds.lfs.co.uk...

[snip]

> But this produces a 'meaningless' primary key value wheras the table
> should (really) have a more meaningfule one based upon the data in said
> table. (Or so I'm told by Data Designers)

Actually, the reverse is true.

There is a page at:

   http://www.cis.ohio-state.edu/~sgomori/570/rdbdesign.html

(Steve Gomori, lecturer at Ohio State University)

which explains database normalisation very clearly (and also provides a link to Codd's 12 rules of database design). This page says the following about creating primary keys:

"As a rule of thumb, if a column value could conceivably change,  do not use it as a primary key. Almost always a key will be data  that has absolutely no purpose or meaning outside of uniquely  identifying a row in a table. Numbers are a typical choice."

OK, this doesn't completely negate your designer's assertion that the primary key should be a meaningful one. The important thing is not whether or not the key is meaningful, but whether or not there's a possibility it might change. I agree that meaningful keys are easier for humans to understand, but it is *essential* that it's based on data in the row which won't change. And meaningful keys have greater potential to change than meaningless keys.

eg. using the "name" column for a table containing information about people is not a good choice, as people's names may conceivably change. But "order number" as a primary key for an order table is probably OK, as it's (probably) essentially a randomly generated number anyway: No point in creating another equally random key instead. But beware if the "order number" has some structure to it: eg. if it encodes the issuing department or similar. In that case it could potentially change and would probably be an unsuitable key. It's up to your designers to determine how unlikely it is that the data used to make the key will change.

Dave.

--
If you reply to this newsgroup posting by email, remove the "nospam"
from my email address first.