4 Replies Latest reply on May 24, 2012 9:36 AM by philmodjunk

    converting flat tournament database to relational

    TroyGowen

      Title

      converting flat tournament database to relational

      Post

      I’m trying to convert an old flat database of tournament results into a relational database so that a user can call up a particular tournament and get the winner/loser results through the history of the tournament, but also be able to call up a particular player and get their individual results in all tournaments in the database.

      I think that this requires at least 3 tables: 1 the tournament event details (tournament name, year, event (singles, doubles, etc.), score, notes; 2 the players with each player’s individual stats and info; and 3 a join table since this is ostensibly a many-to-many situation (each tournament event has from 2-4 players, depending on singles or doubles), while each player can participate in many events throughout their career.

      I have been successful importing back and forth to create a unique EventID for each of the 2,126 records of the original database, as well as a unique PlayerID for each of the 1,262 players that have participated as a winner or loser in these events through the past 100 years or so.

      What I’m not sure of now is how to construct the tables for the final database. I’m not even sure if I’m making unnecessary work of this or not.

      Seems like it should be simple, but I can’t get my head around it.  The original flat database has all the info in a single record, with the fields Winner1, Winner2, Loser1, and Loser2 designating the participants.  I have added new fields, assigning a unique EventID for each of these original records, as well as a unique PlayerID for each player so that Joe Blow is P336 in every Event, whether he is loser or winner.  Obviously, there are multiple PlayerIDs in each record (2-4).

      My problem now is separating these players into their own table in a new database while maintaining the Winner1, Loser1 (etc) relationships to the different Events.

      For instance: One Event might be Men’s Singles at Wimbledon in 1899 when Joe Blow defeated John Doe 6-0, 6-0, 6-1.  I want to be able to call up all Wimbledon winners or losers, but I might also need to call up Joe Blow and see what tournaments he won or lost, etc.

      I’m using Filemaker 11.04 Pro Advanced.

      Thanks in advance for any advice!

      --PS, if any tennis fans can help me, I’ll give you a personal tour of our museum.

       

        • 1. Re: converting flat tournament database to relational
          philmodjunk

          Given the one record for every tournament structure, this won't be simple to do, but it looks like you are on the right track if you have correctly added event and player ID's to the existing table.

          Events---<Player_Event>----Players

          is the minimum structure change needed here. you could even break this down further:

          Tournaments---<Events---<Player_Event>----Players

          And if the data is there, you might even want to break things down to the point where you can see player scores for every match that they played....

          But sticking with this initial structure change, you have two basic options that can be used.

          Option 1. Import records from the existing database over and over again. Each time you change the Target table and field mapping to move the data into the needed records of the tables in your new system.

          Option 2. Move the data with a script that loops through your records. The script can copy data from the existing table into variables and then use that data to create the needed records in your new tables.

          With either method, work it up a small portion of the total data transfer at a time and make lots of back up copies. (I'd test the script with data from just a few tournaments until I was confident that everything was working, then repeat the process with all of the records.)

          • 2. Re: converting flat tournament database to relational
            TroyGowen

            Thanks so much for your help, Phil.

            I feel a bit dense, but I'm still having trouble conceptualizing where to put what data.  I have tried various imports, tables, and layouts, but I have ended up confusing myself more.

            If you are still willing to help, I am uploading some screen shots of the simplest version I have to see if you can give me some pointers.

            The sample record from the flat database shows a typical Doubles Entry.  There are four participants, 2 winners and 2 losers, as well as the info for the event (tournament name, type of event, year, score, and any notes).  The GS number is the unique EventID I created for this specific event.  The "p" numbers above each player's name are the unique PlayerID numbers auto-generated through several targeted imports and back.

            The other screen shot is what I set up via tables in the new database.  I imported all the basic data of the events, including the EventIDs into the table called Event.  I imported the player names and the PlayerID numbers into the table called Players (the LasName/FirstName will be fields I use to separate out the names for alphabetizing and searching later as the original database does not do this).  The Win/Loss table is what I envision as the way to designate the winners and losers associated with each event.

            As it sits right now, the Players table has all the players (misspellings and all) entered over the years and their new PlayerIDs.  The Events table has all the other info (but NO Player info), plus the new EventIDs.  I'm just stupidly stuck on how to relate the two so that I can design a layout in which a particular event will correctly display the winners and losers for that event.  As you can see, I somehow think that Win/Loss table should be in there, but I'm uncertain how or why.

            Apparently, I know just enough about database building to cause trouble, but not enought to get out of it.  ;-)

             

             

            • 3. Re: converting flat tournament database to relational
              TroyGowen

              PS--I know the thing is ugly.  I intend to pretty it up when I get the engine running properly.

              • 4. Re: converting flat tournament database to relational
                philmodjunk

                I'm not sure this will fly with only three tables here, but first lets understand the basic data here.

                Since Fred and Harold won their doubles match, they would presumably advance to the next round and play another doubles match. That, presumably, would make for a second record in the original table, but with a different pair (the winners from another doubles match) and they will be listed as winners or losers in that table. Will that record have the same event ID or a different ID. I am assuming here that it will be a different ID number and the radio buttons for year and event name are what are needed to identify the tournament as opposed to identifying the event (a specific match between two players or two doubles pairs.)

                If my understanding is correct, I'd use just these fields in the WinLoss table:

                _fkEventID
                _fkPlayerID
                OutCome

                OutCome would store one value for "win" and another for "Loss". I might actually put a 1 in this field for wins and 0 for a Loss. (data formatting can display the outcome as "Win" and "Loss" on your layout if you use the options for Boolean formats.)

                For this doubles Match you'd create these records in the WinLoss join table:

                EventID    |    PlayerID   |   Outcome
                GS2         |    p168        |    1
                GS2         |    p792        |    1
                GS2         |    p22          |    0
                GS2         |    P795        |    0

                And for singles matches, you'd have just two records in the join table.

                Using a script that imports records, that requires 4 Imports from your current table. One for Winner1, one for Winner2, one for Loser1 and finally Loser2. After the two imports from the winner field, you use replace field contents to put a 1 in the Outcome field. Do the same for the Imports from Loser Fields, but use Replace Field Contents to put a 0 in the Outcome field.

                Since singles matches will have a pair of empty fields here, I'd perform a find for all doubles matches, do the imports as described above, then pull up the singles matches and do the same, but skip the Import records steps for the second winner and loser fields.