6 Replies Latest reply on Feb 25, 2014 7:30 AM by philmodjunk

    Differences between relationships (1-1, 1-many, many-many)

    RosieUnderwood

      Title

      Differences between relationships (1-1, 1-many, many-many)

      Post

           I am just starting to learn about relationships, and I am confused as the to the differences between the three types….

           I have a practice database already started by someone else which has six tables. The primary key/ unique ID is called individual and this is found in all six of the tables, so does this mean I don't have a foreign key? And what type of relationship is this? Is this a one to one- is that what the relationship links show below?

            

            

            

      Screen_Shot_2014-02-25_at_13.22.52.png

        • 1. Re: Differences between relationships (1-1, 1-many, many-many)
          RosieUnderwood
          /files/2cd5f07959/Screen_Shot_2014-02-25_at_13.22.52.png 117x208
          • 2. Re: Differences between relationships (1-1, 1-many, many-many)
            philmodjunk

                 What you see is FileMaker's guess as to what relationships might be possible. What controls the type of connecter you see (single line, crow's foot, Tee) depends on the field options you select for the match field. If the field is defined to auto-enter a serial number, or has a Unique Values validation defined, you see a single line like you would expect for the "1" in a 1 to many, many to one or 1 to 1 relationship. If the field is unstored or global, you see a "Tee" and this is a unique relations that isn't quite any of the standard text book relationships. If neither field is set up for unique values and is a indexible, stored field, you'll see crows feet on both as there are no built in limitations on the values that can be used. One record on one side of such a relationship can match to many records on the other, but the reverse is also true.

                 Note: A primary key is limited in one way or another to unique values in the table in which it is defined. The ideal primary key uniquely identifies each record, does not have any "encoded meaning" and never, ever changes. A foreign key is the match field in a related table that matches to Primary key in the other field. Foreign keys are not unique as there could be many records with the same value in the field that all match back to the same record in the other table and thus all have the same value in the Foreign Key field.

            • 3. Re: Differences between relationships (1-1, 1-many, many-many)
              RosieUnderwood

                   Okay thanks, so if I have six tables within this one database and they all have the column of individual number but then one table for example is called family and this has family information, another is called genes and contains genetic information and another for example muscular containing data regarding muscular testing then what is the relationship between the six tables- is this a one to one relationship given the primary key- unique ID is the same in each table?

              • 4. Re: Differences between relationships (1-1, 1-many, many-many)
                philmodjunk

                     You wouldn't have the same field as a unique ID in each table. A different field in each table would be defined as the primary key--the unique ID for that table. The simplest primary key to set up in FileMaker is an auto-entered serial number field.

                • 5. Re: Differences between relationships (1-1, 1-many, many-many)
                  RosieUnderwood

                                      Okay when I have gone through the online tutorial this is used as an example:

                  4. In the Customers table, click the Company field and drag a line to the Company field in the Companies table.

                  The Company field is the match field.

                  . Release the mouse button. You see the relationship you have created.

                  The relationship is based on matching data in the Company field in both tables. Records are related when data in the Company field in one table matches data in the Company field in the other table.

                  I thought thats what I was attempting to do matching the individual column within each table to make a relationship

                        

                  • 6. Re: Differences between relationships (1-1, 1-many, many-many)
                    philmodjunk

                         Your information is correct, but this has nothing to do with whether or not the fields being linked are defined as unique IDs.

                         If you have a field that is a primary key (values are unique) and you drag to a field that is not set up for unique values ( foreign key) the above steps produce a one to many relationship. But if both fields are foreign keys (no auto-entered unique values) you get what I call an "indeterminate many to many relationship" where you can get a one to many relationship in either direction but can't set up a "true" many to many relationship as is typically done with  a join table.

                         It is very unusual to link fields in two tables where both are auto-entered unique IDs as there is no way to really control which record in each field will have a value that matches to a particular record in the other table.