Schema design: many to many plus additional one to many

Question

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?

Answer 1

Thus is a perfectly fine design. It is not uncommon for a junction table (AKA intersection table) to have attributes of its own - such as joining_date - and that can include dependent tables. There is, as far as I know, no special name for this arrangement.

One of the reasons why it might feel strange is that these tables frequently don't exist in a logical data model. At that stage they are represented by a many-to-many join notation. It's only when we get to the physical model that we have to materialize the junction table. (Of course many people skip the logical model and go straight to physical.)