RE: the idea of unique primary keys to be a solution that hasoutl ived its usefulness?

[rob@xxxxxxxxx]

I would think the join of compound keys would only be a poor 
implementation without the use of identity columns.

Let's take this for example

Cust     Address
12345    1313 Mockingbird lane
12345    500 Easy Street
56765    232 Clark Street

And then this
Cust     Address                  Call timestamp         clob field of 
discussion
12345    1313 Mockingbird lane    11-03-2003-08.30.00    No answer
12345    1313 Mockingbird lane    11-03-2003-10.15.37    Bought $1,000,000 
worth of new rpg development

Then if you need to change the address you have to remember to change both 
files.  Granted, if you use a referential constraint with CASCADE it might 
do it for you.  But people who program in RPG are loathe to let the system 
do anything for them automatically if they can do it manually.  Therefore 
instead they would add a second field to both of these files that sequence 
the address for that customer.  Therefore it becomes:

Cust     Address#    Address
12345    001         1313 Mockingbird lane
12345    002         500 Easy Street
56765    001         232 Clark Street

And then this
Cust     Address#   Call timestamp         clob field of discussion
12345    001        11-03-2003-08.30.00    No answer
12345    001        11-03-2003-10.15.37    Bought $1,000,000 worth of new 
rpg development

Rob Berendt
-- 
"They that can give up essential liberty to obtain a little temporary 
safety deserve neither liberty nor safety." 
Benjamin Franklin 




"DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx> 
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
11/19/2003 10:53 AM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
"'RPG programming on the AS400 / iSeries'" <rpg400-l@xxxxxxxxxxxx>
cc

Subject
RE: the idea of unique primary keys to be a solution that hasoutl ived its 
usefulness?






Joep,

Yes, I'll agree that compound keys can indeed be considered primary keys. 
I
misspoke; I was thinking of "identity columns" which are commonly used in
databases as primary key. 

The difference between the two designs, imo, is that the widespread use of
compound keys makes for inefficient joins between tables. 

Eric DeLong
Sally Beauty Company
MIS-Project Manager (BSG)
940-898-7863 or ext. 1863



-----Original Message-----
From: Joep Beckeringh [mailto:joep.beckeringh@xxxxxxxxxx]
Sent: Wednesday, November 19, 2003 2:47 AM
To: RPG programming on the AS400 / iSeries
Subject: Re: the idea of unique primary keys to be a solution that
hasoutl ived its usefulness?


----- Original Message -----
From: "DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>
To: "'RPG programming on the AS400 / iSeries'" <rpg400-l@xxxxxxxxxxxx>
Sent: Monday, November 17, 2003 10:24 PM
Subject: RE: the idea of unique primary keys to be a solution that hasoutl
ived its usefulness?


> Unique primary keys are not what most of us use on the iSeries.  We use
> unique compound keys, where the combination of many factors (keys) work
> together to define a unique row.  Unique primary keys are a single field
> that describes a unique row.  These are commonly implemented as UUIDs, 
or
> possibly as auto-incrementing value for such things as order number and
> whatnot...

Who says so? Primary keys can be compound.

>From 'A Guide to the SQL Standard' (C.J. Date / Hugh Darwen):

In SQL, a candidate key definition takes the form
    { PRIMARY KEY | UNIQUE } ( column-commalist ).
For a given base table, at most one candidate key definition can specify
PRIMARY KEY.

Joep Beckeringh

_______________________________________________
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.


_______________________________________________
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.