2 Replies Latest reply on Oct 25, 2013 12:09 PM by davidanders

    Very basic setup question.



      Very basic setup question.


           I am attempting to design and create a database of horse racing data and results. The data comes in 2 comma delimited files:

           entries file

           results file

           I originally planned to just have a flat database and use sort functions to find the data I need. Thinking about what I eventually want to accomplish I realized I need to create a relational database.

           My first table would have a record for each race. (date, track, distance etc etc etc)

           My second table would have a record for each horse that was entered in each race.

           Here is my basic question:

           Each race in table 1 (race table) would be assigned a unique record ID. Would I then use that record ID in my second table for each horse that was in that sepcific race?

           I am in no way a programmer and I am working my way though several filemaker pro books and tutorials but the concepts are foreign to me. I know this is the most basic of questions but appreciate any responses.


           Thomas M.




        • 1. Re: Very basic setup question.

               I think you might also want a table with a record for each Track as well.

               What you describe is actually a "many to many" relationship. A given horse can appear in many races and any given race lists many horses. Thus, neither an ID that uniquely identifies each race nor an ID that uniquely identifies each horse is sufficient to set up the correct relationship. You need both, plus an additional table that we call a "join" table that manages the resulting many to many relationship.

               Start with these relationships:


               Races::__pkRaceIDID = Race_Horse::_fkRaceIDID
               Horses::__pkHorseIDID = Race_Horse::_fkHorseIDID

               You can place a portal to Race_Horse on the Races layout to list and select a Horses record for each given Races record. Fields from Horses can be included in the Portal to show additional info about each selected Horses record and the _fkHorseIDID field can be set up with a value list for selecting Horses records by their ID field.

          • 2. Re: Very basic setup question.

                 The first few links here try to simplify relational database design.