Need help with correct database design for a delicate problem
I think I have a problem in my database design, I am forced to use portal filters for TO's who would just need a Self-Relationship.
What I am trying to do:
I want to have a table Match where I can store matches between 2 football teams.
"Match" Table now looks like this:
__idPk _homeTeamIdFk _awayTeamIdFk _homeUserIdFk _awayUserIdFk goalsHome goalsAway
I also have a join table between "User' and "Match"
Ok, I can get easily all matches for a specific user.
Consider I want to show the wins for a specific User. I have to to a TO with self relationship to User_AllMatches, then I have to add a FILTER to the portal with sth like
(_homeUserIdFk = User::id and homeGoals > awayGoals) or (_awayUserIdFk = User::id and awayGoals > homeGoals)
Even more bad was when I wanted to count the number of wins for a specific user. I had to use a calculation field like this:
ValueCount ( FilterValues ( List ( MatchHomeWins::_homeUserId ) ; __userIdPk ) ) + ValueCount ( FilterValues ( List ( MatchAwayWins::_awayUserId) ; __userIdPk ) )
The problem described is not the only one, and consider when the application gets bigger, I am afraid I will get stuck somewhere and I will need to rethink everything. I also thought at keeping different match records for each user (one for userAway, one for userHome) but this looks even more wrong to me.
What would be your idea ?
Attached is a photo of the current diagram I use for User layout.