Many-to-many tables tracking participation at multiple events

I am working on a side project to track observations of different birds, and the time spent during each observation. Right now I have a table called Observations:

 obs_id   |   start_time  | end_time  |   date      |  location
--------------------------------------------------------------------
    1            3            36         27/09/2016    acadia park
    2            4            00         27/09/2016    zion park
    3            12           50         27/09/2016    acadia park
    4            16           32         27/09/2016    yellowstone
    5            9            20         28/09/2016    zion park
    6            21           50         28/09/2016    yellowstone

However, multiple people are observing these birds, and I would like to be able to query the people associated with these birds. I would also like to keep a running total of each person’s observation time.

So I have a table of Observers, with a column for their total time spent observing (in hours, calculated from the start_time and end_time columns in the Observations table), but I’m not sure of the best way to relate these two tables. I want to relate these two tables so that I can see the observations that each person was involved with.

 person_id   |   total_time  | name  
-----------------------------------------
    1            1.5          Jane Doe        
    2            4            John Doe      
    3            5            James Doe         
    4            6            Jenny Doe        
    5            3            Jeremy Doe

Unfortunately, there can be multiple people at an observation, and the same person can have multiple observations, too. That makes this (if I understand correctly) a many-to-many relationship. I’m wondering how to go about fixing that. Should I make a separate associative entity? Or should I just add a column into Observations that contains a foreign key for Observers, and create new rows for each observer – something like this?

 obs_id   |   person_id  | start_time  |  end_time |   date      |  location
--------------------------------------------------------------------
    1           1_1            3          36          27/09/2016    acadia park
    1           1_2            3          36          27/09/2016    acadia park
    2           2_1            4          00          27/09/2016    zion park
    2           2_2            4          00          27/09/2016    zion park
    2           2_3            4          00          27/09/2016    zion park
    2           2_4            4          00          27/09/2016    zion park
    3           3_1            12         50          28/09/2016    yellowstone

This will be implemented using PHP, allowing users to submit their observations on a website and updating a SQL database (MySQL). I’ve looked at some other problems, like tracking events associated with an item, or tracking student attendance, but none of them have quite the same problem that I do.

Thanks in advance for any help.

Ideally, your system could comprise:

• a Locations table with location information and a location_id
• a People or Observers table with personal details and a person_id
• an Observations table just the way you have it
• and an Observer_Observations table with person_id and obs_id

(i.e. your many-to-many table)

The total_time column in your Observers table should be calculated from Observer_Observations rather than stored.