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?


Without hunt_teams table, a simple query for the teams playing a specific Hunt would require joining players with team_members and grouping them by team_id. A lot more work compared to querying hunt_teams. And that’s probably a query you’ll be running often.

I can also imagine that this additional source of truth will be nice to have in the long run. Running some stats or dealing with eventual inconsistencies in the database will be much easier.

So the first schema is one I would go with 👍

| improve this answer | |

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.