3 Replies Latest reply on May 1, 2009 4:00 PM by philmodjunk

    Generic Notes Table - Multiple relationship paths between two tables

    joeworkman

      Title

      Generic Notes Table - Multiple relationship paths between two tables

      Post

       I am trying to create a generic notes table to contain notes for various elements in my database. The notes could be associated to a person on the "people" table or to a deal on the "deals" table. However, there is a relationship between people & deals already. Therefore when I try to link the People and Deals table to the notes table, I get an error saying that there are more then one relationship path. 

       

      While this is technically true, the purpose of my notes table it just to assign a note to either a deal or a person. How can I do this? Do I have to have two separate notes tables?

       

      Cheers

      Joe 

        • 1. Re: Generic Notes Table - Multiple relationship paths between two tables
          philmodjunk
            

          "Therefore when I try to link the People and Deals table to the notes table, I get an error saying that there are more then one relationship path. "

          You can have more than one relationship between two tables. I've had more than 100 relationships linking the same two tables until I figured out a more efficient design that didn't need so many. :smileysurprised:

           

          Each "table" in your relationship graph is really a "table occurrence" or "table instance" (Same thing different terms). You can only have one relationship between any two table occurrences. To create a 2nd relationship, make a new table occurrence.

           

          I'm using FMP 10, in that version, open Manage | Database | Relationships and click an existing table occurrence. Click the button that has two green overlapping plus signs to make a copy of this TO. Click and drag to make a new relationship that links your new TO to an existing TO.

           

          Make sense?

          • 2. Re: Generic Notes Table - Multiple relationship paths between two tables
            joeworkman
               Seems like an odd way of doing it but its easy enough to do. Thanks!!!! It seems this way that your relationship view can grow very quickly....
            • 3. Re: Generic Notes Table - Multiple relationship paths between two tables
              philmodjunk
                

              It's a pretty standard method for documenting database design. Microsoft Access has a similar tool and they're both based on something called an "Entity Relationship Diagram" that you can find in most database design text books.

               

              Keep in mind that you need a name for each relationship as well as the relationship itself. Each TO thus gives you the ability to distinguish between relationships in scripts, calculations, layouts, value lists, etc.