[AccessD] Membership File Changes

Not that further argument were required, but some dude holding himself up to
be a PhD recommends using composite ids in junction tables.

http://www.youtube.com/watch?v=Z3uTWpuv54U

I love how he says (quoting here) "...that will enable us to create the many
to many relationship between the order and item tables"). I would say, it is
a representation of that relationship, it does not allow anything.

Note that the demo of one person's technique does not make it composite keys
a best practice. 

I would still argue for surrogate keys as the PK in a crosstable, even if
using a composite key as an index. That way the data row retains relevance
even if later one decides to break down further the original FK tables in to
subgroups. One can then, during a data cleanup exercise, relax the data
integrity rules, modify the target table(s), and fix the cross table using
an update query without losing the individual identity of the cross table
records, even if they are temporary orphans. It's just a little extra space
in a database, not the straw that breaks the camel's back.

The video's author also calls a junction a "lookup table", which I think
bespeaks a little loss of credibility, but I gather the misnomer is becoming
commonpalce. 

Anyway, I love how he "casually" says reverse engineering is dropped in
Visio. Wish I had watched this painful video before I laid out $133 for the
software.

%$#%!!!

B.



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

GUID?   I agree - very good for replication and/or multi remote data input
locations.   Not just 
web based either - email, ftp,  offline tablets returned to base and data
transfer via USB 
sticks from remote locations are all common scenarios.

As for cross tables,  I agree with you.  I differentiate between tables
which store Entity and 
Link (Cross) data.   Surrogate PKs on the former are standard.  

In the latter case, they are only useful if the connection between entities
has properties which 
should be stored in a child table.   But as soon as the link has such
properties it becomes an 
Entity in its own right and I would use a SurrogatePK

-- 
Stuart

On 6 Mar 2014 at 20:56, Bill Benson wrote:

> 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? 
> 

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