9 Replies Latest reply on Mar 11, 2010 12:10 PM by comment_1

    Multiple relationships deep

    miw

      Title

      Multiple relationships deep

      Post

      I have been able to build my database efficiently using simple file relationship links when the relationship is only one step away.  I always use the FM assign auto serial number of a table field to be my link to another table (because that ID is always unique and cannot be edited once committed).  Now I am trying to create table which would link to all tables that may be multiple relationships deep; I cannot quite figure out how to do it.  Here is my structure (I am noting only the linked fields and not all the other fields for the table):

       

      TABLES:        Country                    Den                             Type                              Inv

      FIELDS:           CountryID----------<CountryLink>--------<CountryLink  

                                                                  DenID-----------------<DenLink

                                                                                                       TypeID---------???----<TypeLink

       

      I am set with good links, layouts and portals for the Country, Den and Type tables.  Linking the Type table to the Inv table is problematic.  I was hoping I can solely establish the Type link to bring in all the predecessor table information for an Inv record.  However, I cannot figure out how to make that link work in a layout because I need to pick the Type link that would incorporate the descriptive characteritstics (not the serial number IDs which actual form the link because they are not descriptive and since they are multiple tables away, a pop-up menu list doesn't help) of the Type, Den and Country names at one time (I can have many Countries linked to a Type; I can have many Types linked to a Den - so making a Type selection requires knowing the possible combinations to choose the unique Type that incorporates the desired Den and Country).  Any ideas?

       

        • 1. Re: Multiple relationships deep
          philmodjunk

          Can you post an example with some real values for each field? The abstract form of your post makes it difficult to understand.

           

          One design question: If  DenID is a unique serial number, why do you include CountryLink in the same relationship?

           

          "making a Type selection requires knowing the possible combinations to choose the unique Type"

           

          That's where you lost me. Combinations of what?

          • 2. Re: Multiple relationships deep
            miw

            Phil, here is an example of real values:

             

            TABLES:        Country                    Den                             Type                              Inv

            FIELDS:           CountryID----------<CountryLink>--------<CountryLink  

                                                                        DenID-----------------<DenLink

                                                                                                             TypeID---------???----<TypeLink

            EXAMPLES:  Data Values linked to a Type record:

                                    Country                    Den                             Type                              Inv

            example 1:          USA--------------Dime-------------------Mercury

            example 2:          Canada---------Dime-------------------Boat        

            example 3:          USA--------------Dime-------------------Barber

            example 4:          USA--------------Quarter----------------Barber

            example 5:          Euro--------------Dime------------------ Boat

             

            Summary of Where I Am so far:  The way my Types are linked are through the predecessor relationship files (Country and Den) serial IDs.  To record a Type, I first must select a Country (only Countries that have pre-established links to a Den are available in the pop-up menu for this field in the layout).  Once Country is selected, that then allows me to select a Den appropriate only for that Country (through a mirrored Den table).  This works well since Countries can have multiple Dens and Dens can be in multiple Countries.  Type descriptions can also be multiple ("Barber" or "Boat" in the examples above) but what makes each Type unique is not their description but their description plus their links to both Country and Den - thus each Type above is unique. (I test that each Type is unique by concatenating the Type description, CountryID and DenID into a string).

             

            My Problem:  I am stuck getting to the next phase where I want to link an Inv record to a Type.  That is where I need to make a Type selection from the possible combinations of Country, Den and Type as noted above (I should have said a unique Type based on Type description, Country and Den).  For example, I want a Inv record linked to the example 3 combination above - Barber as Type with Dime as Den and Country as USA.  How can I get the layout to help me select the correct Type (showing a pop-up menu based on Type ID in the Inv layout only allows me to see "Barber" as my second field - but how do I know if its "Barber" from example 3 or example 4?).  I do not think a concatenated string works (I use the serialIDs for all links so they are not descriptive, plus if I used a more descriptive concatenated field, that would not be a dynamic link if the description for Country or Den was subsequently edited).

             

            I seem so close yet I am stuck on this one....

             

            Any help you can provide would be greatly appreciated!

            • 3. Re: Multiple relationships deep
              comment_1

              It would be much better if you explained what this is really about - or at least give an example that makes sense. I have no idea what a den is (assuming your solution is not about the sleeping arrangements of lions), and having dens of type Barber or Boat certainly doesn't help. Neither does a cryptic abbrevation like Inv (Invoices? Inventory? Investments?).

               

               


              MIW wrote:

              Countries can have multiple Dens and Dens can be in multiple Countries.


               

              Not according to your diagram: there is a one-to-many relationship between Country and Den.

               


              • 4. Re: Multiple relationships deep
                miw

                A "Den" is short for a coin denomination.  "Inv" is for inventory. 

                 

                 As for Countries having multiple Dens and Dens having mulptiple Countries, I should have been more clear.  Effectively, as you more correctly note, there is a one-to-many relationship between Country and Den.  This is because each Den is unique based on the Den and Country link (in my examples, although there appear to be many "Dime" Dens linked to multiple Countries, that is not the case because each Den is unique based on the Den and Country link.  I my examples, I have a unique Den representing USA and Dime which is linked to mulitple Types.  I also have a unique Den for Canada and Dime and yet another unique Den for Euro and Dime - both of which are ulitmately linked to a Unique Type (Canada-Dime-Boat and a different Type for Euro-Dime-Boat).

                • 5. Re: Multiple relationships deep
                  comment_1
                  OK, that's a good start. So you have (or should have): Countries -< Denominations >- Types Now, how does Inventory figure in this? Assuming this is a coin collection, why not simply add a Quantity field to the Denominations table?
                  • 6. Re: Multiple relationships deep
                    miw

                    Inv[entory] is a table for coins in the collection.  I can't just add a quantity field to the Denominations table because I first need to reference a Type to it so that each coin has a unique Type, Denomination and Country attached to it.  It can't simply add a quanity field to the Type table because each coin has specific attributes (grade, location, etc.) that are unique to the coin and I can multiple coins for each Type.  I built the linked tables because each table has plenty of underlying data specific to Country, Denomination and Type that I would want each coin in the Inv table to be able to reference.

                    • 7. Re: Multiple relationships deep
                      comment_1

                      You are starting to lose me again, I'm afraid. I think you are saying that each coin is a record in the Inventory table? If so, your relationships should look like this:

                       

                      Countries -< Denominations >- Types

                                         |

                                         ^

                                     Inventory

                       

                      Note that each denomination has one country and one type - so by assigning a coin to a denomination, those two are given.

                       

                       

                      • 8. Re: Multiple relationships deep
                        miw

                        Ok - I see your point as to the proper relationship link and now I am on the right track as to the tables and relatinships.  But how on the Inventory layout do I pick the requisite Denomination link that is also appropriately linked to both Country and Type (I need to be able to see a main description from all three linked tables to do the job by choosing the right combination of all three as in any of the examples shown)?  That's where I get stuck.

                        • 9. Re: Multiple relationships deep
                          comment_1

                          This is more of a user interface issue than a relationships one. My preferred method would be to pop up a new window with a list view of the Denominations table (with related fields from the other two tables), and select from there. But there are alternative methods, e.g. a conditional value list or a filtered portal.


                          Coming back to the relationships again: I've taken it as given that you keep a "catalog of all possible coins" in the Denominations table. But perhaps you should consider an alternative:

                                     Denominations
                                          |
                                          ^
                          Countries -< Inventory >- Types