AnsweredAssumed Answered

Need help with correct database design for a delicate problem

Question asked by DoruChiulan on Aug 9, 2014
Latest reply on Aug 12, 2014 by philmodjunk


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

     "User" Table:

                         id                          name

     "Team" Table

                         id                          name


     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.