Re: M:M/Multiple Keys
- From: "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
- Date: Sat, 31 Jan 2009 18:25:31 -0500
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.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"dymondjack" <dymondjack at hot mail dot com> wrote in message
news:904D0C93-BCAA-469F-8E7F-35F71D9EA855@xxxxxxxxxxxxxxxx
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
"Sylvain Lafontaine" wrote:
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.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"dymondjack" <dymondjack at hot mail dot com> wrote in message
news:73D816AE-0EA2-47BE-AAB5-9A1B9149E9F3@xxxxxxxxxxxxxxxx
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
.
- References:
- Re: M:M/Multiple Keys
- From: dymondjack
- Re: M:M/Multiple Keys
- Prev by Date: Re: ODBC Connection to SQL Server Compact
- Next by Date: RE: how do I Filter by Form in a Deployed Database without Access
- Previous by thread: Re: M:M/Multiple Keys
- Next by thread: Re: M:M/Multiple Keys
- Index(es):
Relevant Pages
|
Loading
