Database design for Scavenger Hunt Game

by MikeHolford   Last Updated July 12, 2019 22:06 PM

I'm designing a section of my database which links User, Team and Hunt. I would like users to be members of a team but the team can play multiple hunts (so can be used multiple times).

The first idea involves a 'HuntTeam' which links Hunt to Team directly however I need to know which users within the team will be playing so I've added a 'Player' table like below:

idea 1

The second idea is to remove HuntTeam completely and link Player directly to Hunt so it does the job of linking teams and users playing like below:

enter image description here

I'm worried that although the second option looks more simple, I would need to run quite a few 'belongs to through' or JOIN methods to reference the teams within a Hunt. Would it

Would you recommend one over the other and if so why? Perhaps you have an alternative solution?

Related Questions

Multiple one-to-one relations

Updated April 15, 2016 08:02 AM

Database Design for Products with Bundles of Products

Updated February 18, 2016 02:02 AM

How can I avoid this circular relation?

Updated June 13, 2016 08:02 AM

Can this circular reference be a problem?

Updated May 17, 2019 15:06 PM