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

Title

Need help with correct database design for a delicate problem

Post

     Hi

     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.

Screen_Shot_2014-08-10_at_2.47.18_AM.png

Outcomes