Re: Separate foreign keys with shared ID space

• Post for FREE via your newsreader at post.usenet.com ****

Hi Marshall

"Marshall Spight" <mspight_at_dnai.com> wrote in message news:LPQOc.63922$eM2.25470_at_attbi_s51...
> "Christian Antognini" <christian.antognini_at_trivadis.com> wrote in message
news:410affcf$1_at_post.usenet.com...
> >
> > A PK should have no business meaning.
>
> Says who? Can you justify this statement?

"Unfortunately" I'm some days in holiday... therefore I can reply only today...

Other people already mentioned the advantages of having surrogate keys... in my opinion the most important points are the following:

• Business change to often, therefore a good DB design should be able to assimilate business changes. Of course such a schema is not good according to Codd's rules, but "only" in practice ;-) Some people wrote that changing the primary key is just matter of executing few DDL and DML statements! Such people forget that usually applications that access the DB exist... therefore if a primary key is changed, the foreign keys are changed as well and therefore ALL statements that join the tables which are touched by such a change, should be changed as well!!!!!
• From a performance point of view it is better to have a single-column numeric primary key. This makes look-ups faster and the life of the cost-based optimizer is easier (at least with Oracle... sorry I have to in-depth knowledge of other optimizers...).
• During development a simple primary key makes things easier for developers. In fact if a simple naming convention is used, they don't have to remember or give a look to a DB design each time that they wrote a join.

Chris

PS: there was lot of discussion about SSN... therefore, FYI, in Switzerland, where I live, the SSN is NOT unique.

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

• Usenet.com - The #1 Usenet Newsgroup Service on The Planet! *** http://www.usenet.com Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=