I've this and I am unsure just how it ought to be patterned within the database. The objects I am attempting to model are: teams, gamers, they-player membership, and a listing of costs due for every player on the given team. So, the costs rely on both team and also the player.

So, my current approach may be 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

Within this schema, team_players may be the junction table for teams and players. And also the table team_player_fees has records owed to records towards the junction table.

For instance, playerA is on teamA and it has the costs of $10 and $20 due in August and February. PlayerA can also be on teamB and it has the costs of $25 and $25 due in May and June. Each player/team combination may have a different group of costs.

Questions:

  • Exist possible ways to deal with such a predicament?
  • It is possible to term for this kind of relationship? (in order to google it) Or are conscious of any references concentrating on the same structures?

Thus is really a perfectly fine design. It's not uncommon for any junction table (Also known as intersection table) to possess characteristics of their own - for example joining_date - which may include dependent tables. There's, so far as I understand, no special reputation for this arrangement.

A primary reason why it could feel strange is the fact that these tables frequently don't appear in may well data model. At that point they're symbolized with a many-to-many join notation. It's only if we arrive at the physical model that we must materialize the junction table. (Obviously lots of people skip the logical model and go right to physical.)