8 Replies Latest reply on Sep 25, 2009 3:53 AM by comment_1

    Join table / many-to-many problem



      Join table / many-to-many problem


      I've created a database by importing data from Excel related to horse breeding. Its two main tables are 1)Stallions and 2)Mares (there are others, but these are the main ones).


      The database will be used primarily for analysis and reporting, with a data update to each table once per year to input new statistics.


      Problem: The Stallions table shows stallions (by name & unique ID) by total number of mare services per year and the total outcomes - i.e: one record per stallion per year (with a number of other fields in the table).


      The Mares table shows each individual service of all named mares (by name and unique ID), by stallion (by name and ID), by year - i.e: many records per stallion per year, and single records per mare per year.


      This looks to me like a complicated many-to-many relationship that I assume needs a join table. I want to show, for example, stallion and named mares by year (from the Mares table) together with other related data from fields in the Stallions table.


      Question: Is a join table what I need? If so, is it possible to create and fill a join table using, say Stallion ID and Covering Year as keys, when all of the table data for both tables already exists in the database? (There are several hundred thousand records in Table 2).



        • 1. Re: Join table / many-to-many problem

          You can do this with a Join Table.


          Each record in the table would have, as a minimum, two fields: StallionID and MareID. You would also include any fields specific to that stud service in this table, keeping horse specific data in the two horse tables.


          Thus, Service Date, Results, etc would be part of the Join table and not part of either horse table. To build this table, you might use import records to move data from the current tables into the new join table in order to save having to re-enter so much data by hand. You may also need a script to manipulate the data to help you build links and perhaps identify and remove duplicate records.

          • 2. Re: Join table / many-to-many problem

            I'm a newb with join tables.


            I understand how I would go about creating the join table using import records, but am unclear about its eventual structure and action.


            Could you explain what a single record would look like (or how it would act) if it had these fields: StallionID, MareID, Number of Mares Covered, Covering Year, Missed, Slipped, Live Foal.


            Yesterday I tried relating the two tables using Stallion ID and Foaling Year as the keys. It seems to be producing most of the results I'm looking for, but I don't know if there are any traps in doing this in a many-to-manyrelationship.


            Grateful for your advice.




            • 3. Re: Join table / many-to-many problem
                 It's hard to advise without knowing your purpose. IIUC, you already have your join table: you called it Mares, but it is actually a table of Services. It might be better to have a separate Mares table linked to Services by MareID, so that the name of the mare and any other permanent details would only be stored once.

              Your other table is also not a table of Stallions, since at least potentially (over the years) it has more than one record per stallion. It looks like a table of summaries - which you shouldn't need, IF you had the individual data in the Services table.
              • 4. Re: Join table / many-to-many problem

                Comment and PhilModJunk, thank you


                 I'm still uncertain about how to make PhilModJunk's join table suggestion work.


                I think I'm getting the sense of what comment's suggesting in relation to reshaping the tables and using a Mares and a Services table linked by MareID.


                Solving the Stallions table problem is not quite so straightforward. It's true that it is a table of summaries, but I can't use a new "Services" table as the source because it will contain only the incidences of live births, which is essentially what the current Mares table is. Coverings result in other outcomes - missed, slipped, born dead, died after birth. These are fields in the Stallions table that allow calculation of fertility rates, by year. (And there aren't any mares in the Stallions tables - just summaries).


                Turning to comment's first remark, namely purpose, I'd say that this database is there entirely to enable the integration of data from 4 or 5 disparate sources, and to report on that data by manipulating it in various ways to extract microcosmic pictures of, for example: stallion fertility by year; mares covered by year, by stallion fee band, by stallion domicile, by mare domicile; plus a whole bunch of data and reports from other related tables recording progeny sale prices and aggregates, progeny track performance and cross-referencing stallion progeny sales and track data with related mare progeny statistics. The Stallion/Service/Mare table relationships are primary, but there are many others.


                Hope that adds to the definition. Would seeing the relationships graphs I've put together so far help to define the project better?


                Thanks for your interest and input.


                • 5. Re: Join table / many-to-many problem

                  If you don't have the individual outcome of each service, this is going to be awkward at best. Because the real question is this:

                  What information regarding a stallion can be gathered from the related Mares/Services table? You already have the total number in the Stallions table. If you try to examine a specific stallion/mare combination, you have no specific data to consider.

                  That said, I believe the basic structure should be something like this:

                  StallionStats >- Stallions -< Services >- Mares

                  where StallionStats is your current Stallions table (one record per stallion per year) and Services is your current Mares table (one record per service - in any year).  The Stallions and Mares tables have one record per stallion or mare, respectively.

                  You will probably want to add some auxiliary relationships to filter the related data by selected year/s.

                  • 6. Re: Join table / many-to-many problem

                    Yes, the only individual outcomes available are given by the stallion/mare list in the Mares table. The unsuccessful outcomes are buried in the summaries in the Stallions table.


                    And there is no additional stallion information that can be gathered from the current Mares table. The Stallions table itself lets me report on summaries of stallions by such factors as owners and domiciles, and then in total or more usefully by year, number of mares covered, and fertility rates. From the Mares table, each specific stallion/mare combination, if related to the Stallions table, lets me report on, for example, all mares, by name and domicile, that were covered by which stallion, from which domicile and in which fee band (say, service fee between $30,000 and $49,999 and etc) by year.


                    I will try the structure you suggest. I assume that the Stallions and Mares tables should contain only the stallion and mare IDs and/or names.


                    On that basis, I think the auxiliary relationships will be simplified too, because each of the lists like progeny sales and progeny track performance contain either stallion or mare name, or both. I'll report on progress.


                    Thanks again...




                    • 7. Re: Join table / many-to-many problem

                      I've tried the structure, and can't quite get the result you suggested, namely: StallionStats >- Stallions -< Services >- Mares


                      What I get is: StallionStats >- Stallions -< Services >< Mares.When I try some Find Requests on a "Mares"-based layout involving one related field from the "Stallions" side, I'm getting really anomalous results, and I don't understand why. (e.g. out-of-range cover fees and foaling years in the found set).


                      I then built a pair of relationships between StallionStats and Services using two keys: Stallion Name, and Foaling Year. Stallion Name exists in both tables. Foaling Year is a field in Services and a calculation field in StallionStats. No join tables, no ID number keys.


                      Interestingly, there were no anomalous inclusions in the found sets of a number of complex finds. I don't get it!

                      • 8. Re: Join table / many-to-many problem

                        freeflight wrote:

                        What I get is: StallionStats >- Stallions -< Services >< Mares.

                        The relationship between Services and Mares should be:


                        Services::MareID = Mares::MareID


                        and MareID should be unique in in the Mares table.




                        Note that the relationhip between Mares and Stalliions (and consequently, between Mares and StallionStats) is a many-to-many, through the Services join table. Finding in the Mares table using related fields from Stallions is affected by that (it will search all related records, no matter through which Service they are linked). I believe that most of your finds and reports should be based on the Services table.