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
Re: [ale] MySQL and 3NF
From what I understand. Move the volunteer_id from the contributor table. put the contributor_id inside the volunteer table. This way you can have a 1-to-many relationship between the two. --- Get inside Atlanta's Tech Scene - http://www.atlantageek.com 'Business Intelligence' is not an Oxymoron - http://www.intelliforge.com On Wed, 30 Jan 2002, David S. Jackson wrote: 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 --- 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. - 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
Re: MySQL and 3NF
Hi. I would type museum: department or likewise in name field if its not so many it would work nicely. But thats just me:) /PM\ David S. Jackson wrote: 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
RE: MySQL and 3NF
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