choosing relations! (1 Viewer)

jolontour

New member
Local time
Yesterday, 23:59
Joined
May 22, 2015
Messages
1
Hi.

I want to build a database with Access to manage volunteers who will be helping out at a number of events. The data-entry will be contact-centric, with contacts/volunteers having one or more events assigned to them.

I've previously done similar using a one-to-many relationship between two tables: the main volunteer/contact form contains an event-subform. It's simple enough then to query all contacts assigned to any particular event.

What I really want is to understand how using a many-to-many relationship (which I guess could equally apply) would add power or flexibility to what I could do or how I could query the tables. I have the feeling it must offer advantages - but I'm not quite sure what these are.

Many thanks for your help with this one.

Cheers - Jol
 

namliam

The Mailman - AWF VIP
Local time
Today, 07:59
Joined
Aug 11, 2003
Messages
11,696
You dont "choose" relationsships they are determined by the requirements... it is either there or it isnt.... you dont just randomly pick a 1:n or n:m relationsship.

Having said that, a n:m in database terms doesnt really exist if your requirements do require a n:m relationsship... you solve it in the database by inserting an extra table. Called a junction table.

Volenteer - Junction table - Event

This would be 1:n - m:1 effectively making 2 1:n relationsships
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 01:59
Joined
Feb 28, 2001
Messages
27,001
Many/Many relations occur for cases where you have two entity types and what is sometimes called (in management terms) a matrix organization (as opposed to a hierarchical or pyramidal organization).

Prime example using your stated problem: The M/M relation occurs when you can assign more than one person to an event and one person can support more than one event. To implement this, you cannot do a 1/M and M/1 table at the same time using only the tables for persons and for events. Access doesn't support that as a relation. It is not one of the options you can choose in a relationship. However, you can simulate it by having a third table called the JUNCTION table (but watch out because JUNCTION used in another context is an Access keyword.)

In an ordinary hierarchy environment, you have a prime key (PK) in the highest level of the implied pyramid and all child tables use the value of the PK as their foreign key (FK) linking them to a specific parent record. This is a case of M/1 where the child is the M side and the parent is the 1 side.

In the M/M relationship, you have a table of events with a regular PK and a table of persons with a regular PK. You create the junction table, which in its simplest form is two fields. Each field is an FK, one to the person and one to the event.

Then when you want to see who is helping with event X, you form a JOIN between the person table and the junction table, then group by the event FK in the junction table. (The person FK supports the join of persons to the junction table so you could do a report.) The records for event X tell you the persons supporting it.

If instead you wanted to see which events person Y was supporting, you would form the JOIN between the junction and event tables and group by the person FK in the junction table. Then the joined records would let you list events associated with person Y.

Technically, each FK in the junction table is in a M/1 with the table corresponding to that key. So the junction table is M/1 with the person table through its person FK and the junction table is M/1 with the event table through its event FK.

There will be at least one record in the junction table for each person who is currently assigned to support an event - one record per event. There will be at least one record in the junction table for each event having an assigned support person - one per person.

It is possible that if assignments have not yet been made that the junction table will have no entries for the events or person. That is, the junction table is SPARSELY POPULATED (or just SPARSE). And that is OK.

Given that (a) event/person associations can be made at any time after the individual person and event records are created (b) not every person is assigned to each event and (c) not every event is supported by every person, the size of the junction table will be far less than the size of the event table times the size of the person table.
 

Users who are viewing this thread

Top Bottom