M:M/Multiple Keys

[dymondjack]

Sorry everyone... I know this horse has been being beating for a long long
time, but despite all of my research, I'm still having trouble with a
decision on primary keys with many-to-many relationships.

I'm making my first attempt at a M:M relationship: Companies and Contacts
(one company, many contacts; one contact, many companies (rare in my case,
but it does happen)).

So I've got this junction table (in process of being) setup. Not too big of
a deal, I think I've got a decent handle on it (thanks MVPs), and I want to
make sure I'm not getting ready to kick myself later on. There's two
different ways I've seen these junction tables set up, with one field as a
PK, and with 2 fields.

1 Field example:
jtblCompCont
(0) fldCompContID <-- PK
(1) fldCompID <-- Child
(2) fldContID <-- Child

2 Field example:
jtblCompCont
(0) fldCompID <-- PK & Child
(1) fldContID <--PK & Child

Logic tells me to go with a 2 field PK, I think it makes more sense. (if I
want to assign a Title to the contact within that company, the 2 field
primary key makes sure that there will only be one contact ID for that
company, whereas the the 1 field key would allow me to duplicate that
information (bad bad bad, right?)).

The thing that worries me is that as I get deeper and deeper into the design
of this db, if I go with composite keys, at some point I'm going to wind up
with tables that require many more key fields that I want (or am allowed, if
I understand correctly access has a limit of 10 fields per key?). I don't
necessarily expect this problem with Companies and Contacts, but when I get
into Machines, Operations, Tools, Fixtures, Parts, etc, etc, I can see where
composite PKs could get pretty ugly...

But, if I go with a single field for a key, technically this would allow
duplicate records, but relating these tables down the line will be no issue
at all (one parent field, one child field... nice and simple).

I suspect the answer is to use a single field as a PK and be very careful of
data entry (leave it up to me to make sure there's no duplicates rather than
access).

Can anyone shed some light on this based on my scenario? I've never worked
with composite keys before, so I don't really know what I would be getting
myself into there.

Sorry for the long post but I've been pulling my hair out for the last two
days trying to get this figured out.

--
Jack Leach
www.tristatemachine.com

- "A designer knows he has reached perfection not when there is nothing left
to add, but when there is nothing left to take away." - Antoine De Saint
Exupery

 

[Douglas J. Steele]

Couldn't you have asked a safer question, like what's the best religion, or
which political party's best? <g>

This really is a contentious issue. I almost always use the second approach,
because it's unlikely I'm going to cascade keys to the point where I have
more than ten. If you're using the first approach, though, you can prevent
the problem of potential duplicates by creating a Unique Index on the
combination of fldCompID and fldContID. While you can only have one Primary
Key index, you can have multiple Unique indexes.

 

[Sylvain Lafontaine]

Your first example is the new school where the second one is the old one.
In the old school, saving space is a premium and is even more important than
the time of the programmer; hence the use of a composite primary key derived
from the two foreign keys. The new school don't bother itself with the
saving of space - amongst other things - and will use a separate primary key
for the relationship table.

As you have already noticed, one of the problems with the use of composite
keys is that while your design become deeper and deeper, it's no longer
sufficient and you must add other fields to it. If you have other tables
related to it, not only they make harder the writing of code but as you add
other fields to it, you also have to update all these other tables and the
code that access them as well. This include not only the code in the
database itself but in the interface as well.

So, if you're using composite keys, many times when you'll make a
significant change to your design, you will have to cascade this change all
over to other tables, queries and code; not only in the database but also to
any interfaces used to work with it. If you are using independant primary
keys for all of your tables, the modifications that you'll have to do will
be striclty limited to only those directly associated with the change to
your design.

I stopped using composite keys many years ago and since then, half of my
problems with designing and building databases has vanished.

 

[dymondjack]

Thanks for the reply... this confirms some of the issues I thought I might
run into if I went this route. I remember when I first got into this db
stuff I had a similar arguement with myself, and wound up deciding to never
use a composite key unless I had no other choice.

Many-to-many relationships are a brand new thing for me, and even though a
single field Key seems to bend the rules of normilaztion a little bit in this
case, I think I'm going to have to go with it, regardless.

Sometimes it helps to have someone with experience in the matter confirm
what I can only speculate without having tried it over a long-term.

Thanks again.


--
Jack Leach
www.tristatemachine.com

- "A designer knows he has reached perfection not when there is nothing left
to add, but when there is nothing left to take away." - Antoine De Saint
Exupery

 

[dymondjack]

This really is a contentious issue.

Sorry about that... I had assumed it might be, reading through however many
posts and sites in the past couple days, but I was having trouble coming up
with with comparisons on a general level, where most people had a particular
issue trying to get it going, one way or the other.

Unfortunately, I think I'm going to have to go with the single field
Primary, though I still don't think it's the *right* way to do it.

Also, I wasn't aware that Unique Indexes could be spread over more than one
field. This will definately help some, even if I feel I'm bending the rules
a little.

Thanks!!

btw... <g>?? grin maybe? i'm not quite up on all this stuff yet...


thanks again!


--
Jack Leach
www.tristatemachine.com

- "A designer knows he has reached perfection not when there is nothing left
to add, but when there is nothing left to take away." - Antoine De Saint
Exupery

 

[Sylvain Lafontaine]

Depends on your definition of normalization. If you take a look at the
latest technologies - Cloud database services, Microsoft Azur Plateform and
the SyncService - you'll see that things like the value of a primary key is
no longer considered as part of your data.