1 Reply Latest reply on Aug 14, 2012 2:34 PM by davidanders

    Many to Many to Many to Many........

    YoramErez

      Title

      Many to Many to Many to Many........

      Post

      Hello Everyone.

      This is a one big and wonderful forum.

      I will appreciate your help here. I'm trying to solve this issue for a few days.

      (Excuse me for my English)

      I have 19 Tables in one DataBase and I need to connect all the tables together so I will be able to connect any field or record in one table to any other field or record in any other table. Basically everything in this DB needs to be connected with anything.

      I tried to add a join tables (you can see the attached pic) but non of it solve my problem.

      Can anyone refer me to a link that will help me with this?

      Or If it's not so complicated, I Would love to have a brief explanation

      Thank you all...

      Yoram.

      Options.jpg

        • 1. Re: Many to Many to Many to Many........
          davidanders

          http://help.filemaker.com/app/answers/detail/a_id/9922/~/understanding-and-creating-many-to-many-relationships-in-filemaker-pro

          SNIP

          In relational database design, a many-to-many relationship is not allowed.  Consider the example of keeping track of invoices.  If there were “many” invoices with the same invoice number and one of your many customers called up asking about that invoice number; how would you know which specific one they were referring to?  The answer is you could not.  An invoice should have a unique “ID.”

          To get around the problem of having a many-to-many relationship you need to break apart the many-to-many relationship into two one-to-many relationships.  Using a third table, commonly called a “join table”, does this.  Each record in the “join table” would have the foreign key fields of the two tables it is joining together.  Nothing special needs to be done with the foreign key fields in the join table as they will get populated with data from the other two tables as records are created.  It is not uncommon for a join table to have a lot of records in it – since records are created in the join table as records are created in the two tables it joins.

          SNIP

          http://help.filemaker.com/app/answers/detail/a_id/3247/~/relational-database-design-101-%28part-2-of-3%29

          SNIP

          The diagram in figure 6 also indicates that there is a many-to-many relationship between Invoices and Products. You cannot set up a many-to-many relationship directly between two files.

          For example, if you create a relationship between Invoices and Products based in a field named Invoice ID, each product can appear on only one order. If you create a relationship based on a field named Product ID, each invoice can have only one product

          SNIP

          White Paper for FMP Novices
          http://www.foundationdbs.com/downloads.html

           

          http://en.wikipedia.org/wiki/Database_normalization
          Database normalization is the process of organizing the fields and tables of a relational database to minimize redundancy and dependency. Normalization usually involves dividing large tables into smaller (and less redundant) tables and defining relationships between them. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.