[ale] MySQL and 3NF

[David S. Jackson]

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.