Many to many relationship

1

I've worked with databases before but nothing this complicated. I'm setting up a database for a local organization. The organization puts on weekend retreats, the retreats are run by teams which are pulled from a list of previous participants. The people who attend the weekends are then added to the list from which they may be pulled to work on one or more weekends on different jobs. This information needed is 2-fold:

  1. list of all participants who worked on a weekend sorted by which jobs each participant did
  2. find a weekend with a list of who was doing what job

My structure is this:

WEEKENDS
Weekend_ID Weekend_Date Location Job_ID

JOBS
Job_ID Job_NAME

PARTICIPANTS
Participant_ID First_Name Last_Name Email Weekend_ID

PARTICIPANTS_JOBS
PJ_ID Job_ID Participant_ID Weekend_ID

WEEKENDS_JOBS
Weekend_ID PJ_ID Job_ID

Does this make sense? Am I missing something? Is it normalized properly?

Thanks for your help

2

Your structure doesn't make sence to me but maybe I don't understand you correctly. I want to give you this advise:

  1. What if the organisation wants to start a 7 days retreats? What will happen to your stucture? Can you make it more general so you can use it for more requirements if they change?
  2. Your table for JOBS is clear te me, but what is the Job_ID doing in the WEEKENDS table? And why is the Weekend_ID in the PARTICIPANTS table? For me it looks like you have way to much relations created and you can drop some of them.
3
  1. Changing the number of days for the retreat does nothing to change this structure except to renaming anything named Weekends.

2.The Job_ID field in the WEEKENDS table is a Foreign Key so the two tables can be linked. Same for the Weekend_ID field in the PARTICIPANTS table. The table I'm not sure is necessary is WEEKENDS_JOBS.

4

One important thing about a database is that you store data once. If you are a participants of a weekend where do you store that data? I can query the participants_jobs table but also the participants table. If I have 2 retreats should the PARTICIPANTS table 2 records? And if I cancel the weekend do you delete the participants record or the PARTICIPANTS_JOBS table and what happens to the record in the WEEKENDS_JOBS table then?

5

The Participants table stores the info for everyone who has made a weekend. Their info doesn't get entered until they've actually made the weekend so if either the weekend is cancelled or the person cancels, they are not in the database.
Where I'm getting confused is how to connect the info for the participants with the weekends and with the jobs. The workers on the weekends are pulled from the list of people who've already made a weekend. They can work more than 1 weekend so they can be connected to many weekends. But I also need to know which job they did on which weekend, so they can also be connected to many jobs.

6

Rogier is correct. Your data modeling does not follow rules of normalization.

You need something closer to below. I'm not 100% sure if participant and job_"doer" are the same thing or not.


RETREATS
    retreat_id (PK)
    start_date 
    end_date 
    location_id 
    description

LOCATIONS
    location_id (PK)
    location_name
    description

JOBS
    job_id (PK)
    job_name 
    start_date 
    end_date  
    description 

PERSONS
    person_id (PK)
    title (mr/ms/etc.)
    first_name
    last_name
    email 
    joined_date

RETREAT_PERSONS 
    retreat_id  (!! NOT NOT NOT Retreat_Persons_ID !!)
    person_id 
    job_id (job/"function" that person played in that retreat)
    (retreat_id, person_id ) (PK)  <OR>--depends
    (retreat_id, job_id ) (PK)  <OR>--depends
7

There are two main types of tables: "master" tables and "intersection"/"child" tables. The first type identifies a specific entity, such as RETREATS or PERSONS. The other provides details about a specific combination of entities.

All data in a table must relate to the key of that table -- in fact, to the key, the whole key and nothing but that key!

You iterate your way through the design, using the steps of normalization.

!!And NOT EVERY TABLE NEEDS AN ID BASED ON THE TABLE NAME!! That's the most common myth in table "design". Avoid it to get a better design! Instead, for intersection tables, use as keys of the main entities as the (leading) key of the intersecting tables.

1 Like
8

This helps a lot. I was getting caught up in trying to figure out which of the participants table or weekend table was the parent and which was the child, and of course they are both parent tables. Also, the reminder that not every table needs an ID is helpful. Thanks for the insight.

Question: since the Jobs table relates to both the Weekends and the Participants, can it be the child of both?

9

Yes, you could likely have two child tables: one for weekend_jobs and one for participant_jobs.

Which makes it all the more important that there's a separate jobs table that has the base info for the job. Then you can have multiple, separate child tables that give you info about the relationship of that base job to other entities.

10

Ok that makes sense. I do have a separate look-up table for jobs.
Thanks again.