14 Replies Latest reply on Mar 21, 2013 8:28 AM by LabsRock

    Linking one table to several others

    dunkake

      I'm building some sort of document mgmt section.

      The idea is to have just one document table (with a container field for the actual document).

       

      I’m stuck with the solution to link one “document record” e.g. to an invoice record and another to a contact record (given that “invoices” and “contacts” are separate tables).

       

      I can’t just have a "generic foreign ID" field in the documents table and put in one case an “invoice ID” in it and in another case a “contact ID”, can I?

       

      Thanks in advance for your hints!

        • 1. Re: Linking one table to several others
          Mike_Mitchell

          Hello, dunkake.

           

          The short answer is yes, you can use the same foreign key from two parent tables. However, you may be in a bit of a spot if you ever want to link the same child record to both parent tables. In such a case, you won't be able to because you only have one foreign key.

           

          With that in mind, when I have situations like this, I normally put a foreign key in the child table for each parent table to which I might want to link it.

           

          HTH

           

          Mike

           

          Edit: Another option is to use a multi-key in your child table, where you put in a return-delimited list of keys. That can work. Or, you can use a join table and create a record in the join table for each join between parent and child. That would be the most flexible option, but requires a bit more maintenance on your part.

          • 2. Re: Linking one table to several others
            dunkake

            Hi Mike,

             

            thanks for the hint!

            I just saw a relationship I want in SugarCRM where you “tell” a task that it is related to a project or a customer etc.

            Unfortunately, I couldn’t get the underlying database structure description.

             

            Anyway, multiple fIDs it is. Thanks again!

             

            Regards

            Christoph

            • 3. Re: Linking one table to several others
              dunkake

              Haven’t noticed the edit…

              With a join table I would only forward the problem there (I should create several foreign ID fields there then), right?

               

              edit: would you create something like a "document allocation" join table?

              In the example with fID_invoice, fID_contact and fID_document?

              • 4. Re: Linking one table to several others
                Mike_Mitchell

                No, in the case of a join table, you only have a single foreign key. To accommodate the multiple parent tables, you'd have one record for each combination. So you'd have one record for the join between invoice and document, and another for the join between contact and document. There are a couple of benefits of doing it this way:

                 

                1) If you ever need to add another table to the structure, you just create a join to the same field. So it's much more expandable.

                 

                2) If you need to add information that's specific to the join, you can put it in the join table. For example, if you want to put information about the specifics of the join between the invoice and the document that doesn't apply to the join between the contact and the document, you put that in the join table and it only shows up on that join record.

                 

                Of course, if neither of those applies, then it may not do you much good. So you can evaluate what works best for the situation.

                 

                Mike

                1 of 1 people found this helpful
                • 5. Re: Linking one table to several others
                  debi

                  Dunkake,

                   

                  If you're thinking of using a multi-valued key, I would only recommend doing that from the parent side - and then, only if you expect the parent to have multiple children. Using it from the child side could be confusing: if you have fk "3" - is that the primary key for the contact or the invoice?

                   

                  Whether or not you use a join table, I think you need to be more explicit and consider using a separate field for each type of foreign key (id_contact, id_invoicie), or provide some way for FileMaker to know what table the key refers to. For instance, you could create a code field and insert INV or CON, depending on the type of parent, and then use a muliple-predicate relationship.

                   

                  Debi Rubel

                  FullCity Consulting

                  1 of 1 people found this helpful
                  • 6. Re: Linking one table to several others
                    Mike_Mitchell

                    Good points, Debi. (Personally, I'm not a big fan of multi-keys; I find them confusing and hard to maintain, but when giants like Ray Cologon advocate their use, well, I'm not brave enough to contradict.)   

                     

                    Mike

                    • 7. Re: Linking one table to several others
                      LabsRock

                      Hi Debi,

                      Sorry to just jump in here, perhaps somewhat off-topic, but what is a multi-predicate relationship?  I haven't heard that expression before.

                       

                      Also, when I use muti-keys I will also make sure there is a separate primary key with a single unique value in each record.  I usually setup multi-key fields to be AE values with Replace existing value and then have separate fields containing the individual components.  Are there better ways to get the additional keys entered?

                       

                      Craig

                      • 8. Re: Linking one table to several others

                        Hi Craig,

                         

                        "I usually setup multi-key fields to be AE values with Replace existing value"

                         

                        Since keys can never change, why auto-enter?  Using auto-enter ( replace ) just means the records must evaluate needlessly and you also run the risk of losing their value whereas a stored calculation is more dependable and will always update if the referenced field changes. 

                         

                        The only time auto-enter ( Replace ) is needed is if the resulting value might need to be changed by User.

                        • 9. Re: Linking one table to several others
                          dunkake

                          Hi Debi,

                           

                          good point, thanks!

                           

                          The existing part of the system uses identifiable IDs (e.g. 12INV378 for an invoice). So that wouldn’t be a problem.

                          The only thing that concerns me about a join table are difficulties with “document portals” since that would have to be a nested portal (e.g. a layout that shows invoices with a portal that shows the join table would have to have another portal inside to show documents) , right?

                           

                          Christoph

                          • 10. Re: Linking one table to several others
                            Mike_Mitchell

                            No, Christoph, that's not correct. You can show fields many tables away in the same portal, as long as they're related.

                             

                            Mike

                            • 11. Re: Linking one table to several others
                              debi

                              Craig,

                               

                              "what is a multi-predicate relationship"

                               

                              That's a relationship that uses more than one key on at least one side of the relationship. So in this example, in the dialog in the RG for linking keys, you might show pk_Invoice = fk as well as INV = Code (from Invoice on "left" to DocumentsJoin on "right").

                               

                              "I usually setup multi-key fields to be AE values with Replace existing value"

                               

                              Like LaRetta, I'm curious about that. Sometimes I use multi-key fields - usually globals - to hold multiple values - great for creating a related set on the fly. But I always do that by script, not auto-enter.

                               

                              Christoph,

                               

                              "The existing part of the system uses identifiable IDs (e.g. 12INV378 for an invoice)."

                               

                              That should work, if the keys are set as text. If they are set as numbers, the text is more visual clarity than structural integrity. You may want to double-check that a contact with ID 12CON378 does not seem to own the same document.

                               

                              Debi Rubel

                              FullCity Consulting

                              • 12. Re: Linking one table to several others

                                Of course the other benefit of auto-enter (replace) is when requiring unique from two different fields (using validation unique on the concatenated field) but that wouldn't apply here since the unique serial will already be part of the ID..

                                • 13. Re: Linking one table to several others
                                  dunkake

                                  Dear all,

                                   

                                  thanks so much!

                                  This was my first “ask for help” here and I’m absolutely puzzled with the quality and promptness of the answers.

                                   

                                  Hope to be able to return the favour in the future!

                                  • 14. Re: Linking one table to several others
                                    LabsRock

                                    Sorry for not getting back on this sooner, but I wanted to respond and thank you for clarifying what a muti-predicate relationship is.  I've used them quite a bit and now have a name (correct name).

                                     

                                    As for using AE Calcs, this is a habit I picked up quite a while back in an older version.  I don't recall the specifics, but I was getting better results using AE Calcs than an unstored calculation.  I wish I could provide more details; this may have been something that really isn't an issue any longer or perhaps was due to some quirk in the system I was working on.  It had to do with how quickly/reliably the values were updating and I think I just never had any reason to switch.  In all honesty I don't use multi-keys much in the systems I'm currently working on.

                                     

                                    I'm primarily an in-house developer and I don't get out much to converse with other FM folks.  So this Forum is great for making me feel a little less isolated.

                                     

                                    Thanks again for letting me join in.

                                     

                                    Craig