>So, one of my associates has made a linking table (some people also call it
>intersection table, cross tab table, but i believe that the propper way to
>model a many to many relationship is via a "linking table"). in the linking
>table, there is no primary key defined.
Let us suppose you wish to define a many-to-many relationship between
people and telephones (and it IS a many-to-many relationship: most
people have at least a home and a work phone (and some also have a
home cellphone and a work cellphone), and most families do not have
a separate phone for each family member).
You have a 'people' table with names, birth dates, employee number,
etc. The primary key is the employee number (id int not null
auto_increment). (Hint: Social Security numbers are NOT guaranteed
unique, even if everyone relevant has one.)
You have a 'telephone' table with a telephone number, type (landline
or cellular), owner (company or someone else), location, and an
artificial primary key (id int not null auto_increment). Why not
use the telephone number as primary key? Some extensions, such as
the one in the lobby for guests, or the ones in elevators, can't
take incoming calls and don't HAVE numbers in the conventional sense.
So how do you represent the relationship? Add another table
("linking table" is a reasonable description) containing two columns,
the primary key of the person and the primary key of the telephone.
So what is a suitable primary key for THIS table? It's a key on
both columns. The same person can't have the same telephone more
than once, so requiring uniqueness prevents multiple identical rows
that don't make any sense. Should the key be (person_id, telephone_id)
or (telephone_id, person_id)? This depends on which index you
anticipate the SQL query to need most often.
Now, to those who like bringing up the foreign keys as some sort
of alternative: I don't believe it. Having foreign keys gets you
referential integrity. It does NOT eliminate the need for the
"linking table", nor does it change what's in the table, to represent
a many-to-many relationship. I challenge anyone to demonstrate
otherwise.
>I believe that every table must have
>a primary key. It is absolutely essential, otherwise you'll get tons of
>problems including redundancy, and inconsistency.
Yes, you could record the relationship between me and my work telephone
several times, if the primary key didn't prevent that. I don't believe
you have to have a SINGLE-FIELD primary key on every table.
>However, my associate
>believes that our coding will ensure that such problems will be avoided and
>that it's okay for a table to have no primary key defined. I totally
>disagree. Even if our code is perfect, a primary key must be defined.
>
>So, am i correct in being concerned, or am i just being close minded?
>If, i'm totally wrong, in what situations is it a good idea, okay, or
>benificial to not have a primary key defined for a table?
I can't think of a good reason to not have a primary key.
Gordon L. Burditt
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
