1 Reply Latest reply on Dec 5, 2011 10:37 AM by philmodjunk

    Table Occurrences - a check on my understanding

    JohnMike

      Title

      Table Occurrences - a check on my understanding

      Post

      Dear All,

      I have inherited a FMP 11 db and I am trying to get my head around it. So we have established that in the relationship diagram we see the base tables and the table occurrences relating to those base tables.

      There are about 100 base tables and many table occurrences. Some base tables have a single occurrence i.e. the base table only others have many occurrences.

      A check on my understanding:

      • All relationships should resolve to one to many (many to one).
      • A one to one should effectively be a field and a many to many to be broken down to two one to many relationships.
      • A parent is on the 'one' side of the relationship and a child is on the 'many' side of the relationship.
      • If a relationship between a parent and child is created then:
        • We can access a parent field and e.g. display it on a layout, from any related child record.
        • We can access a given set of child records from a parent using a portal or by creating a table occurrence where the relationship between the parent and child is established. We can subsequently utilise various relationship functionalities in conjunction with scripts.
      So here is the question that I am struggling with at the moment:
       
      None of the 100 or so base tables are related to each other every relationship is defined as a new table occurrence this implies that every relationship is defined on the many or child side:
       
      • Am I correct in assuming that the data only exists once in the base table a new table occurance merely establishes the relationship?
      • I can see the advantage in terms of the layout of of the relationship table in not having relationships between the base table in that it rapidly becomes totally unreadable. However is there an overhead in approaching relationships in this manner? I ask as some layouts load slowly.
      • Is this an accepted approach managing the relationships?
      • Are we missing out on any FMP capabilities by omitting to have any parent / child relationships between the base tables?
      • Is this efficient?
      I hope that I have explained myself clearly and would gratefully receive any feed back.
       
      Regards
       
       

       

        • 1. Re: Table Occurrences - a check on my understanding
          philmodjunk

          All relationships should resolve to one to many (many to one).
          A one to one should effectively be a field and a many to many to be broken down to two one to many relationships.

          A one to one relationship may be set just like a one to many relationship--just with only one matching record on both sides. This can be enforced with a field validation on both key fields specifying Unique Values only.

          If a relationship between a parent and child is created then:
          We can access a parent field and e.g. display it on a layout, from any related child record.
          We can access a given set of child records from a parent using a portal

          Yes, you can place fields from teh parent table occurrence on a layout based on the child table occurrence and it will correctly display data. If you were to put the field from a child table occurrence on the parent layout, you'd see data from the "first" related record but not from any subsequent records. Displaying multiple child records on such a layout requires a portal.

          ..or by creating a table occurrence where the relationship between the parent and child is established.

          As I and another have explained in response to what you posted today, this is true, but you need to do more than just switch to the child layout, you have to do more such as performing a find or using Go To Related Record to create a found set of records that match your current record on the Parent layout. This can be scripted so that changing layouts automatically produces the foundset consistent with a parent table occurrence's current record.

          None of the 100 or so base tables are related to each other

          Correct, only table occurrences are used to link the base tables in relationships. The occurrence may have exactly the same name as the base table and you may have a layout with exactly the same name that refers to this occurrence in Layout Setup, but the boxes found in the relationship map are all table occurrences. This makes it possible to define as many different relationships between the same pair of base tables as you need.

          You may find this article on the Anchor Buoy method helpful. It describes a method for organizing table occurrences and relationships in a way that can make them easier to work with: http://sixfriedrice.com/wp/six-fried-rice-methodology-part-2-anchor-buoy-and-data-structures/

          There are many reasons why data on a given layout loads slowly. You'll need to analyze not only the relationships involved, but all the details of this layout design such as conditional formats, filtered portals, summary fields that total up values for large numbers of records, Scripted finds and/or sorts that kick in automatically when you access the layout (and which might reference an unstored field--making it even slower). That's not a complete list of possibilities but may give you some ideas on what to check next.

          I also recommend getting a copy of FileMaker Advanced if you do not already have such a copy. It's script debugger and database design report will be very helpful in assisting your analysis of this database system.