9 Replies Latest reply on May 19, 2016 6:31 AM by beverly

    More than instances of one foreign key

    cillion

      Hi.

      Lets say that we have the database with tables PEOPLE, COMPANY, PHONE and EMAIL. Both PEOPLE and COMPANY could possibly have an email and phone. Now I choose to have fk_email and fk_phone in both PEOPLE and COMPANY, and this results in this error:

       

      2016-05-19_10-23-33.jpg

       

      How would you guys set up this relationship?

       

      Thanks for all inputs

        • 1. Re: More than instances of one foreign key
          beverly

          Yes, the Table Occurrences on the RG (relationship graph) are just 'views' into the tables you have created. You may place the same (TO) on the RG many times, but it must be named (like an alias) to something unique each time.

           

          I also have the same (phone and email can relate to people or companies, as well as addresses) design.

           

          Using the TO (just another view) again, with another name is absolutely the correct method. However, you might even have a Join Table (or two) that would help resolve this kind of problem and avoid "duplicate" TOs. I'd have to think about how, exactly. But without knowing your exact relationships now (or how your interface works), it would be difficult. You can re-think your design yourself!

           

          beverly

          • 2. Re: More than instances of one foreign key
            bigtom

            You are creating a relationship loop somewhere. a screenshot of the graph will help a lot.

             

            You need an additional TO somewhere, but with the info you have provided it is tough to say where. Customer_Account Table is not a table you mentioned, but that one is involved in the error.

            • 3. Re: More than instances of one foreign key
              cillion

              customer_account is the customer. Just trying to translate. Basicly both the PERSON and CUSTOMER have MOBIL, PHONE and EMAIL

              2016-05-19_12-04-40.jpg

              • 4. Re: More than instances of one foreign key
                thomas_staehli

                As bigtom said, you are trying to create a loop which is not allowed ( Account -> Mobil -> Person -> Telefon -> Account ).

                Screen Shot 2016-05-19 at 13.34.51.png

                 

                Now there are a lot of different ways to structure the relationship graph, but in your case I would suggest splitting it in two or more graphs, with always the main table occurrences on the left (the ones on which your layouts will be based), and the linked tables occurrences  on the right with a prefix that allows you to understand to which base table the occurence is linked to.

                 

                Screen Shot 2016-05-19 at 13.38.31.png

                 

                I hope this helps

                1 of 1 people found this helpful
                • 5. Re: More than instances of one foreign key
                  cillion

                  Thanks for you input. Okey, I think am just alittle comfused since I base my knowledge on datamodeling from school. As we speek im watching the DevCon 2015: Data Modeling Beyond Anchor Buoy - Jason Young that made me realize that many filemaker developers use the Anchor Buoy way of thinking. I adapt this in my example, so this is the "correct" way?:

                  2016-05-19_14-18-37.jpg

                  • 6. Re: More than instances of one foreign key
                    thomas_staehli

                    As I said before, there are a lot of different ways that would be correct. Let's say this is a very common correct way :-)

                     

                    One thing though, I would recommend adding prefixes to the table occurrences names instead of keeping the original name with numbers... Depending on how big your app grows, at some point you won't be able to know if the field from MOBIL 2 is linked to PERSON or to ACCOUNT.

                    Again there are different ways to add prefixes, like using the full name in lowercase or uppercase,  using the first 3 letters  etc...

                     

                    acc_MOBIL

                    acc_TELEFON

                    acc_EPOST

                     

                    per_MOBIL

                    per_TELEFON

                    per_EPOST

                     

                    This way you'll always now straight away from which relationships the fields are coming from.

                    1 of 1 people found this helpful
                    • 7. Re: More than instances of one foreign key
                      cillion

                      Thanks so much. This is great stuff! Yes I understand that in a big solution I should focus on naming the tables on a good way.

                       

                      But when am now going to add for example a mobile number to a PERSON, then I just need to add the Mobile field from MOBILE 2 into the PERSON layout? (Because I will now only create layout for the anchors). ANd FM will make automaticly make the foreign key?

                       

                      Prevoiusly I have just added the foregin key to the layout with a foreign key selector to make the relation.

                      • 8. Re: More than instances of one foreign key
                        erolst

                        in this very example I would suggest using a single “Communication” table with a type field, rather than maintain one table for every communication type under the sun …

                        • 9. Re: More than instances of one foreign key
                          beverly

                          My preference is to use a single table for communication (phone and email) as well (possibly websites). You must have a type & location (home phone, work email, etc.) this may be one or two fields. The you have to query for these types. It may be sufficient to have separate fields (as OP has) and have one-to-one relationship to the contacts (companies or people).

                           

                          COMMUNICATION (fields)