4 Replies Latest reply on Feb 14, 2011 3:06 PM by philmodjunk

    Understanding relationships



      Understanding relationships


      Hi, I am just learning FMpro, so forgive me if this is all very simple.

      I want to understand many to many relationships and how to handle them cleanly in FM. So, two tables; Concerts and Songs. Obviously a concert can contain many songs, and also a song can be used at many concerts. The table for concerts has fields for the date of the concert and so on, and the table for songs includes words for the songs etc. So I want to link Concerts and Songs TOs so that a layout based on Concerts will show me the songs used at a particular concert. Also I want to look at a song in a layout based on Songs and see which concerts it has been played at. 

      Sounds simple. I have read that since this is a many to many relationship I should create a table between these two, perhaps called playlists. Yet that doesn't seem to help as a playlist would have many songs and a song would be on many playlists. Is is necessary to create another table, or can I link Concerts to Songs in a relationship based on a primary key in Concerts and a foreign key in Songs?

      Thanks for any help you can give.

        • 1. Re: Understanding relationships

          "can I link Concerts to Songs in a relationship based on a primary key in Concerts  and a foreign key in Songs?" - No.

          You need to create a join table, with two fields - Song and Concert.  It lists every song that was played at every concert.  'Playlist', as you say.

          • 2. Re: Understanding relationships

            Here's a demo file that illustrates what Sorbsbuster is tallking about:  http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html

            It matches "contracts" to "companies" but if you change those names to "Concerts" and "Songs" it's structure will illustrate the concept. The third, "join table" would be your "playlist" table.

            • 3. Re: Understanding relationships

              Thank you both for this. I have found this hard to grasp, so the example was very useful. It is good to see that I only need to have the ID fields of both tables in the join table, and that I can have fields in the join table which are not in the other two tables. Thank you again.

              If you don't mind explaining, why is a many to many relationship without a join table a bad idea? 

              • 4. Re: Understanding relationships

                How would you link a song record to many concert records and also be able to link many songs to the same concert record if you didn't?

                In FileMaker there is a way to do this, but it's not nearly so easy to work with.

                It is good to see that I only need to have the ID fields of both tables in the join table

                That's the minimum needed. It's often the case that you find you need additional fields in the join table that can be used to document details of that specific pairing of the two records. If you wanted to identify who is performing a song at a given concert, for example, you'd need a field in the join table to record that data.