Shawn,
Yes, we *are* on the same page, now that I understand that you didn't mean to
implement the many-to-many relationship in a single table.
Stuart,
Read what Stuart has described in this note. The association table is the one he calls
TItle_Member. This table is *crucial* to the design, not just a "nice-to-have". It
gets one new row every time a member gets an additional job and records the member_id
of the member and the title_id of the job. This is the *heart* of the many-to-many
relationship; it's how you know which jobs are held by which people and which people
hold which jobs.
With this table, you can get the member_id of every person who has a particular job
and the job_id of every job ever done by a given member_id. If you need to know the
name of the person rather than their member_id, you simply join to the Member table
using the member_id foreign key of Member_Title. By the same token, if you need to
know the job name, you join to the Title table using the title_id. Naturally, you do
both joins if you want to know both the job name and the member name.
If you use this design, you should be able to store any information you want very
concisely and get back anything you want to know very easily. Your primary and foreign
keys will make sense and will be easily enforced.
Is everything clear now?
Believe me, the Systems community has a lot of experience with the issues raised by
many-to-many relationships and this is the way we've been handling those issues for
many years now.
Rhino
----- Original Message -----
From: [EMAIL PROTECTED]
To: Rhino
Cc: [EMAIL PROTECTED] ; Stuart Felenstein
Sent: Friday, August 27, 2004 2:05 PM
Subject: Re: Many to Many: Does this make sense ?
Rhino,
You and I are on the same page. I also never seen a "true many-to-many" relationship
in database design. The closest approximation I have seen is the 3 table design like
you and I have both proposed. It's the only way I have ever seen to model a
many-to-many design, that's why I called it that. Sorry for my semantic faux pas (You
say po-tay-to, I say po-tah-to..... ;-)
As to the subject at hand, relational data structures:
The "one-to-many" relationship. This is what typically exists between items that are
in a parent-child relationship. Examples of this could be a person and their phone
numbers, a business and it's employees, a discussion topic and its responses. Notice
how there is always one item relating to several other items? Hence the name
one-to-many. Let's say you wanted a database that can store peoples names and all of
their phone numbers. You could create one table with every possible column for each
type of phone this person has. (I am leaving out the column type definitions to save
space)
CREATE TABLE person (
Name,
Title,
Address,
City,
State,
Zip,
HousePhone,
CellPhone,
FaxPhone,
OfficePhone,
SecretaryPhone,
GaragePhone,
DoctorPhone
)
But what happens when you need to add a new type of phone number? You would be
forced to change your table design and possibly several sections of code. This is a
BAD design and should never happen. What you need are two tables, one for personal
information, and one for phone numbers.
CREATE TABLE person (
ID
Name,
Title,
Address,
City,
State,
ZIP
)
CREATE TABLE PhoneNumber (
person_ID,
Number,
Type
)
Each entry in the PhoneNumber table will equate a person to a number and identify
what type of phone number it is. Can you see any reasonable limits to how many
different numbers you can store for each person with this type of design?(of course
there are limits to how many records a database can hold be we aren't talking about
those). Where the single-table model limited you to just a few, very particular, phone
numbers the two-table model allows you complete flexibility.
Let's examine your case of members and titles. Basically you will have certain
people with common job titles (Database Analyst, Janitor, Receptionist, etc.) and
duplicating that information over and over again in your database takes up lots of
space but it could fit into a two-table model. It would look basically like this:
CREATE TABLE member (
ID,
Name,
... other fields ...
)
CREATE TABLE title (
member_ID,
JobTitle
)
To avoid storing the same JobTitle multiple time in your title table, you would need
to change your design so that all titles are stored only once and create an
association table to link members to their titles.
CREATE TABLE member (
ID,
Name,
... other fields ...
)
CREATE TABLE title (
ID,
JobTitle
)
CREATE TABLE title_member(
title_ID,
member_ID
)
This kind of design will allow each JobTitle to be associated with multiple members
and each member can be associated with multiple JobTitles (many items of one kind can
associate with many items of another kind, or "the items participate in a many-to-many
relationship"). Each association of a member to a job title (or job title to a member,
depending on how you want to look at it) is an entry in the title_member table. Here
is a sample of how some data might look.
member title_member title
+----+------+ +----------+-----------+ +----+------------+
| ID | Name | | title_id | member_id | | ID | JobTitle |
+----+------+ +----------+-----------+ +----+------------+
| 1 | John | | 1 | 1 | | 1 | Janitor |
| 2 | Mary | | 1 | 2 | | 2 | Secretary |
| 3 | Sam | | 2 | 3 | | 3 | Dog Walker |
| 4 | Jane | | 3 | 3 | | 4 | Astronaut |
+----+------+ | 3 | 4 | +----+------------+
+----------+-----------+
Two people have been Janitors, John and Mary. Sam has been a Secretary and a Dog
Walker. Jane has also been a Dog Walker. So far, nobody has been an Astronaut. This
type of design gives you both flexibility and control as each element (a member or a
title) exists only once in your data structures. To be more descriptive, we could
call the "title_member" table something else like "WorkHistory" or "PositionsHeld" or
"PreviousTitles" (imagination encouraged).
Does this help you with the bigger picture? I know I can ramble off-topic and into
the wrong direction (I have done it before) so let me know where I missed, please?
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Rhino" <[EMAIL PROTECTED]> wrote on 08/27/2004 12:58:52 PM:
> My remarks are interspersed below for reasons which will probably be obvious.
>
--------8<------- clipped for space --------8<----------------
> Rhino
