converting flat tournament database to relational
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.