[ale] MySQL and 3NF

[Tommie M. Jones]

>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                        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.