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
