5 Replies Latest reply on Oct 5, 2012 7:44 AM by Oliver_Reid

    One-to-One relationship


      What is the best way in filemaker to create and handle a one-to-one relationship?


      Say, for example, in OOP I would have a base class called Contact which has 90% of the information that a contact needs, but some contact types need additional fields. So for example, we might have a DateOfBirth field for a Client class which would derive from Contact, but have a SpecialistType for a Doctor class which would also derive from Contact.


      To create a fully normalized database, I would normally have a seperate table for Client with the ContactID as the foreign key. Is this best practice for filemaker too? Are there any issues to be aware of when hiding and showing the fields when the Contact type field is changed?





        • 1. Re: One-to-One relationship

          The primiary-foreign key method is correct.


          However, if there are only a few secondary fields that you're pushing to the other table - is it really worth it? The overhead associated with a few fields is likely pretty low. If it were a 1:many relationship then definitely do it that way.


          Hiding and displaying fields based on a selection type is a little more work. You cannot natively hide or show fields based on some criteria. That's been a wish for a long time.


          What you'll probably want to do is use a tab panel, with each of the tabs named and the appropriate fields on each tab. You can then set the tab panel with the same fill colour as the layout, remove all lines and set the tab width to zero - effectively hiding the fields. Have the default tab to be completely blank.


          Put a script trigger on your selection field that does a Go To Object step. The object being the name of the tab you want to display.

          • 2. Re: One-to-One relationship

            Some developers have started exploring EAV (entity-attribute-value) as a way to add these kinds of values.


            You would have one table with:

                 contactID, "DateOfBirth", "6/4/2001"

                 contactID, "SpecialistType", "..."



            Then you'd need to relate by the ID and the 'attribute'. This is where an ExecuteSQL to get the value would be handy! (or a filtered single-row portal)


            Others (before we had filtered portals and ExecuteSQL) would create the fields (in same table or different one-one table). Then by layouts (and/or tab panels on layouts), we'd limit what fields are used and how they are placed. And you may use this display with the EAV fields, too.



            • 3. Re: One-to-One relationship

              Thank you!

              • 4. Re: One-to-One relationship

                Thank you!

                • 5. Re: One-to-One relationship

                  When you have a record with large number of fields loading could be slower as I believe FM always loads the entire record. For the extreme in efficieny you could put just the key ID fieldsalong with any unstored or global fields in one table and each stored attrbute its own related table. Extreme but likeley approaching SQL speeds for data retrieval?