[ale] MySQL and 3NF

[Mazukna, Thomas]

the theoretically correct way of doing this is:

1. drop volunteer_id from contributors table.
2. create a link table with two fields:
contributtor_id
volunteer_id

this way you can have many to many relationship.
one volunteer can be in many contributors and vice versa

Tomas



-----Original Message-----
From: David S. Jackson [mailto:deepbsd at earthlink.net]
To: ale at ale.org
Sent: Wednesday, January 30, 2002 11:15 AM
To: kplug-list at kernel-panic.org; ale at ale.org; mysql at lists.mysql.com
Subject: [ale] MySQL and 3NF


Hi,

I came across a relationship between entities that I hadn't
counted on, and I'm trying to adjust my database tables to handle
this new relationship.  I need some help with visualizing and
implementing this relationship into the database design.

The database is for an inventory of contributions to be auctioned
off for a Montessori school.

Here are my tables:

mysql> show tables;
+----------------------+
| Tables in vmscatalog |
+----------------------+
| Category             |
| Contact              |
| Contributors         |
| Delivery             |
| Groups               |
| Item                 |
| Volunteer            |
+----------------------+

I've assumed that each contributor (business, individual,
whatever) would have only one volunteer from the Montessori
school that they would be dealing with.  so I've got the
following structure for the contributors table:

mysql> describe Contributors;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra
|
+----------------+--------------+------+-----+---------+----------------+
| Contributor_ID | tinyint(3)   |      | PRI | 0       |
auto_increment |
| Name           | varchar(100) |      |     |         |
|
| Street_Address | varchar(50)  | YES  |     | NULL    |
|
| City           | varchar(20)  | YES  |     | NULL    |
|
| State          | varchar(5)   | YES  |     | NULL    |
|
| Zip            | mediumint(8) | YES  |     | NULL    |
|
| Contact_ID     | tinyint(3)   | YES  |     | NULL    |
|
| Volunteer_ID   | tinyint(3)   | YES  |     | NULL    |
|
+----------------+--------------+------+-----+---------+----------------+
8 rows in set (0.01 sec)

Likewise, the Volunteer_ID ties in with a Volunteer table and a
unique row in the volunteer table.

Now I've got a situation where a large Museum actually has two
people from the school each talking to different departments in
the Museum, each donating a different set of gift certificates.
So I have to figure out some way to let the contributors' table
handle more than Contact_ID and more than one Volunteer_ID.

Don't I want each Contact_ID field for each record to be a single
discrete ID number?  How would you guys handle this?

TIA!

-- 
David S. Jackson                        dsj at dsj.net
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
It's hard to get ivory in Africa, but in Alabama
the Tuscaloosa.  -- Groucho Marx

---
This message has been sent through the ALE general discussion list.
See http://www.ale.org/mailing-lists.shtml for more info. Problems should be

sent to listmaster at ale dot org.

---
This message has been sent through the ALE general discussion list.
See http://www.ale.org/mailing-lists.shtml for more info. Problems should be 
sent to listmaster at ale dot org.