I have a dilemma in how the data in this scenario should be organised, as well as queried.
The main elements in the scenario are the following:
Each player plays for one particular team.
Each team participates in one or more competitions.
First I'd like to know how this data should be stored within WordPress, using Custom Post Types, Taxonomies, or maybe custom tables?
Next comes querying. Let's say I want to display all players who play in the |Eurocup| competition, and I also want next to each player displayed, his team name and info about team.
Finally, the whole system should prevent redundancy of data. That is, if for example I remove a team from one of the competitions, the players within the team should be automatically disassociated from that competition. Same if the team description changes, the player list should also display the updated team description.
Maybe there is a simple solution to this data storage and relationship in WordPress and I'm missing it, so I'd really appreciate some tips on how to implement this.