8 Replies Latest reply on Aug 12, 2014 8:53 AM by philmodjunk

    Need help with correct database design for a delicate problem

    DoruChiulan

      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

        • 1. Re: Need help with correct database design for a delicate problem
          philmodjunk

               I gather from the terminology, that this isn't American Rules Football...wink

               Which makes some of your terminology unfamiliar to me. So I have a few questions about your design and the purpose of this database.

               What is a "user"? It's not a team, so I don't get what a record in this table represents. Perhaps a "fantasy" system?

               And how do you record which team wins a given match? Using the fields for the number of away and home goals as match fields in your relationships between User_AllMatch and the two "...wins" table occurrences puzzles me. I don't see how that is intended to function.

               In a typical team sporting event, a game or "match" table links to two occurrences of a Team table, one link for the away team and one for the home team. Two score fields record the score for the away and home teams. Comparing the scores--which can be done in more than one way in FileMaker determines which team gets the "win" for that match.

          • 2. Re: Need help with correct database design for a delicate problem
            DoruChiulan

                 User is just like a facebook user. I play FIFA on Xbox. And we have "gamerTags".

                 Let's say I play a match with my friend and I want to add a record in the database.

                 My gamertag/user is QWE, his ABC.

                 When we play a match, I add a new record in the Match table like this:

                 1(id), Real Madrid(home_teamID), Man Utd (away_teamId), QWE(home_UserId), ABC(away_UserId),3(home_goals),1(away_goals).

                 This means QWE played a game with ABC, QWE used Real Madrid team, ABC used Man Utd team, QWE scored 3 goals, ABC 1 goal.

                 So a result would look like this:

                 QWE (Real Madrid) 3 - 1 ABC(Man Utd)

                 Another match

                 QWE(Chelsea) 0 - 3 ABC (Liverpool)

                 Another match (vs other user)

                 XYZ (Aresnal) 3 - 1 QWE (Chelsea)

                 This would be User_AllMatch for QWE user.

                 If I want to get the wins I add a portal related to User_AllMatch where I user a filter like this:

                 (UserAllMatch::_homeUserIdFk = User::__userIdPk and UserAllMatch::homeGoals > UserAllMatch::awayGoals) or (UserAllMatch::_awayUserIdFk = User::__userIdPk and UserAllMatch::awayGoals > UserAllMatch::homeGoals)

                 This means give me all records from user matches where (home user id is current user Id and homeGoals > awayGoals) or (away user id is current user id and awayGoals > homeGoals).

                 If self relationship would allow me OR operator between conditions would be great. Unfortunately we can use only "AND".

                 I hope the problem is more clear now. Thanks for all your help and sorry for providing incomplete details.

                 PS. Forget about the two "..wins" TO, I already deleted them. I was using those for some other details, but I decided I won't need that.

            • 3. Re: Need help with correct database design for a delicate problem
              philmodjunk

                   So this is a Fantasy system of sorts. It would seem that each "user" simulates being the "owner" of one or more soccer teams and thus "Their" team will play another gamer's team in a match. Is that a correct analysis?

                   If so, it would seem that you need to link each user record to one or more team records rather than directly linking them to a match record. The teams selected for the match would be linked to the match record rather than the users.

                   But since this is a database to support a game system, maybe you pick and choose your teams each time that you play and thus each user is not directly linked to a team that they "own"?

              • 4. Re: Need help with correct database design for a delicate problem
                DoruChiulan

                     Yes each time a user plays, he can choose another team. I cannot make a one-to-one relationship between user-teams.

                     Don't know if there is a better design for this.

                • 5. Re: Need help with correct database design for a delicate problem
                  philmodjunk

                       Well, I had two different designs in mind and how users linked to teams is the key difference here. This does require linking users directly to matches rather than linking them to teams that then link to matches.

                       But the whole "away vs. Home" designation complicates this as a user might select a team and play as the "Away" team in one match and select a different team and play as the "Home" team in another match.

                       And I assume that you want the total wins/losses statistics for each user?

                       With a break down for wins/losses Away vs. Wins/Losses Home?

                  • 6. Re: Need help with correct database design for a delicate problem
                    philmodjunk

                         And what version of FileMaker are you using? If you are using FileMaker 12 or newer, we have the option of using ExecuteSQL to get the desired win/loss statistics here.

                    • 7. Re: Need help with correct database design for a delicate problem
                      DoruChiulan

                           Yes I am using FileMaker 13. That's great.

                           So the database design is fine ?

                      • 8. Re: Need help with correct database design for a delicate problem
                        philmodjunk

                             Since you have indicated that you've changed your design, I don't know exactly what your current design is and I don't know what results you need out of it. That's why I asked some questions yesterday about the results that you want to see.

                             What I see here suggests that you may need two sets of table occurrences (called table occurrence groups), one for data entry--setting up a match and recording the score for each team/user pair. and one for reporting the win loss statistics that you want. (And my questions were to verify exactly what statistics you want to see here.)