Re: Question on Primary Keys

In our last episode (Mon, 16 Oct 2000 12:11:52 GMT), the artist formerly known as Jan Lenders said:
>There are database "gurus" who claim that you should never ever use
>class attributes as primary key.

Sometimes they're right; that's why they get called "gurus" :-).

>Instead you add a DBMS-generated primary key value column to each and
>every table. They even claim that these keys should be unique within
>the database; if a Client_Num value 12345 exists, you should not use
>Order_Num 12345.

The problem comes when people that Don't Get Databases get into the picture, and start playing around encoding information into these keys.

Unfortunately there are a _lot_ of people that Don't Get Databases, at least not far enough to understand that the notion of a "primary key" has implications of what forcibly _must_ be in the field.

>The reason was that primary key values may never change (agree) and
>that an object might become of a different class sometime (disagree;
>I've never seen Clients who became an Order).

I agree with you that a Client is unlikely to become an Order. The problem doesn't come with that: it more commonly comes with the assignment of things like Cost Centres or Profit Centres, where if the company starts playing the Reorganization Game, which many big companies do regularly, strange anomalies can and will pop up.

Or it comes when people don't understand the implications of deleting something. At work, the Systems Security department seems to be pretty gleeful about deleting users if they should go away. This has been known to cause serious problems in the change management system, as we're left with orphaned changes that no longer have a user associated with them.

We're just lucky that the ERP system doesn't use an "On Delete Cascade" clause in defining connections between tables; were that the case, the deletion of users could result in deleting all sorts of data, perhaps extending to programs on the system.

Mind you, if that happened once or twice, and payroll couldn't be made because SysSec ("oops!") deleted a bunch of critical programs, they might well learn to restrain their glee...

-- 
(concatenate 'string "cbbrowne" "_at_" "hex.net")
<http://www.ntlug.org/~cbbrowne/>
"The dinosaurs died because they didn't have a space program."
-- Arthur C Clarke