1

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.

1

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.

  • Thanks for the response! But how would total_time be calculated from Observer_Observations? Wouldn't it be calculated from the start_time and end_time columns in Observers? – ale19 Apr 17 '16 at 17:16
  • 1
    @Alice you mean Observations? Yes, you have to join Observations and Observer_Observations (and maybe Observers as well) to output a calculated total time. – Ziggy Crueltyfree Zeitgeister Apr 17 '16 at 21:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.