[ale] MySQL and 3NF

[John Wells]

The problem with this approach is that one volunteer
would be exclusively involved with one
contributor...which might and might not be the case.

What you really need is two cross-reference tables:
something like ContactAllocation and
VolunteerAllocation.

These would contain (Contributor_ID, Contact_ID) and
(Contributor_ID, Volunteer_ID) respectively.

That way, you can have a many-to-many relationship in
both situations.  

John

--- "Tommie M. Jones" <tj at atlantageek.com> wrote:
> 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.
> 


__________________________________________________
Do You Yahoo!?
Great stuff seeking new owners in Yahoo! Auctions! 
http://auctions.yahoo.com

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