[ale] MySQL and 3NF

[Jim Philips]

My quick and dirty solution would be to create a second Volunteer_ID
field in the table, such as Volunteer_ID2. Would that work?


On Wed, 2002-01-30 at 11:15, 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                        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.