[AccessD] Membership File Changes

Bill Benson bensonforums at gmail.com
Thu Mar 6 19:56:27 CST 2014


Can't resist jumping in here, maybe I will learn something... My own
experience is too limited to claim to be an expert in this matter. If you
are going to jump down my throat, please take your boots off first.

AFAIK, GUID is used for only two reasons:  (1) When a relational database is
distributed in more than one location via replication and (2) when some
transactions are created client-side then handed back to the server via web
methods. I would expect GUID to be relatively poor, but in some situations
reliable whereas getting the next higher number through autonumber (or a
sequence on the database) may not be so. 

As for multi-field PKs, I think where they make sense is in cross tables?

If I have a subscription that consists of a Customer and a Publication, I
can usually prevent duplicate subscriptions by keeping track only of
CustomerID and PublicationID. If John Jones has ordered The Economist, then
I would keep a composite ID in the Subscriptions table of FKCustomerID and
FKPublicationID.

Of course, this begs the question what happens when John Jones orders The
Economist for his three sisters. But in that more complex situation, it
would just change the FKCustomerID to instead be FKRecipientID and we arrive
at the same deduction:  we really do not need a SubscriptionID and it serves
no purpose either in terms of speeding up the database, nor giving us any
descriptive information.

For those who contend our database acts any faster with it, I would ask, how
so? 


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David McAfee
Sent: Thursday, March 06, 2014 8:02 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Membership File Changes

<snip>
This strategy is not without detractors however, mostly "old timers" from
databases where multi-field PKs were ALWAYS used. Mostly they try to argue
that it is possible to just read the PK down in the child / grandchild /
great grandchild etc. simply by looking at the data in the multi-field PK,
which "saves your bacon" when orphans creep in and so forth.  Additionally
the "values" can often be pulled for reporting and the like, without
requiring joins back up the chain.  Both are true, but not (IMO) make up
for the overhead and headaches that multi-field PKs bring with them.
</snip>

John, I was recently called an old timer because I insisted on using
autonumber integer PK for some of our new designs.
The young whipper snapper said that's the "old way" of doing things, and is
very anal about using GUIDS and multi-field PKs.

I know GUIDs have their place, but he just doesn't get it. I love how my
stuff runs so much faster, even with joins. :)

D
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list