Circuit design: many to many, plus one to many
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, team memberships, and a list of fees for each player on that team. Thus, the fee depends on the team and the player.
So my current approach is this:
**teams**
id
name
**players**
id
name
**team_players**
id
player_id
team_id
**team_player_fees**
id
team_players_id
amount
send_reminder_on
In this diagram team_players
- a table of connections for teams
and players
. And the table team_player_fees
has entries related to the entries in the connection table.
For example, playerA is on Team A and has royalties of $ 10 and $ 20 due in August and February. PlayerA is also on Team B and has a commission of $ 25 and $ 25 in May and June. Each player / team combination may have a different set of fees.
Questions:
- Are there any better ways to handle such a scenario?
- Is there a term for this type of relationship? (so I can google it) or find out any links with similar structures?
a source to share
Thus, it is a wonderful design. It is not uncommon for a join table (AKA intersection table) to have its own attributes - for example joining_date
- and which may include dependent tables. In my opinion, there is no specific name for this agreement.
One of the reasons why this might seem strange is that these tables often do not exist in the logical data model. At this stage, they are represented by multiply connected designations. Only when we get to the physical model should we materialize the join table. (Of course, a lot of people skip the logical model and go straight to the physical.)
a source to share