Create two association tables: Contributor +----------------+--------------+------+-----+---------+----------------+ | 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 | | +----------------+--------------+------+-----+---------+----------------+
New tables: Contributor_Contact +----------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+--------------+------+-----+---------+----------------+ | Contributor_ID | tinyint(3) | | PRI | 0 | | Contact_ID | tinyint(3) | | PRI | 0 | +----------------+--------------+------+-----+---------+----------------+ Contributor_Volunteer +----------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+--------------+------+-----+---------+----------------+ | Contributor_ID | tinyint(3) | | PRI | 0 | | Volunteer_ID | tinyint(3) | | PRI | 0 | +----------------+--------------+------+-----+---------+----------------+ -- Greg Johnson -----Original Message----- From: David S. Jackson [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 30, 2002 11:15 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: 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 [EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= It's hard to get ivory in Africa, but in Alabama the Tuscaloosa. -- Groucho Marx --------------------------------------------------------------------- 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 --------------------------------------------------------------------- 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
