Schema design: many to many plus additional one to many
- by chrisj
Hi,
I have this scenario and I'm not sure exactly how it should be modeled in the database. The objects I'm trying to model are: teams, players, the team-player membership, and a list of fees due for each player on a given team. So, the fees depend on both the team and the player.
So, my current approach is the following:
**teams**
id
name
**players**
id
name
**team_players**
id
player_id
team_id
**team_player_fees**
id
team_players_id
amount
send_reminder_on
Schema layout ERD
In this schema, team_players is the junction table for teams and players. And the table team_player_fees has records that belong to records to the junction table.
For example, playerA is on teamA and has the fees of $10 and $20 due in Aug and Feb. PlayerA is also on teamB and has the fees of $25 and $25 due in May and June. Each player/team combination can have a different set of fees.
Questions:
Are there better ways to handle such
a scenario?
Is there a term for this type of
relationship? (so I can google it) Or know of any references with similar structures?
