8 Replies Latest reply on Apr 26, 2009 6:34 AM by comment_1

    Relationship Link Problem

    Col_uk

      Title

      Relationship Link Problem

      Post

      Hi

       

      I'm currently working on a data base of my music and I'm struggling to get a relationship to show album titles in a portal based on the track title, I have two tables called Albums and Tracks, as the names suggest the albums table contains all my album records and the tracks table contains all the track records, I've managed to create the relationship to show all the track info in a portal on the albums layout but I can't create a relationship the other way round it only shows one album name not all the albums the track appears on.

       

      I hope this makes sense and I would appreciate your help

       

      Thanks

      Colin

      Using 8.5 advanced

        • 1. Re: Relationship Link Problem
          Jade
            

          Hi Colin,

           

          Since several tracks are on one album and a track can appear on many different albums, you need to setup a many-to-many relationship.  There are a couple of ways to do this but it is standardly done by adding a 'join' table between the Albums and Tracks (I will call it Album-Tracks for this explanation).  

           

          If you haven't already added a unique key value to each table (i.e. Album_id and Track_id as auto-incrementing unique numbers) it will help a lot if you do so now.   The 3 tables can now be linked by their keys and the join table need only contain the Album_id and Track_id fields.  So the table occurrences should look something like this in the Relationship Graph:

           

          Albums -< Album-Tracks >- Tracks 

           

          The portals can now be based on the Album-Tracks table occurrence.  Since all the tables are related, you can display the fields you want in the portal (e.g.: Tracks::TrackName ).

           

          Hope this yields the results you want… 

          • 2. Re: Relationship Link Problem
            Col_uk
              

            Hi Jade

             

            Thanks for your reply,  i tried your suggestion and all my layouts lost their links to their tables, obviousley i've done some thing wrong

            What i did was create the new table called Album_Tracks, and created two fields in that table called Album_ID & Track_ID

            Created the relationship as you suggested Album_Tracks to Albums using the Album_ID and Album_Tracks to Tracks using the Track_ID

             

            As i'm new to all this can i ask a couple of what are probably stupid questions, 

             

            1, Does the new table Album_Tracks require any records?

             

            2, How does the link work as the album id will not be the same as the track id?

             

            Thanks for your time Jade

             

            Thanks

            Colin

             

             

             

             

            • 3. Re: Relationship Link Problem
              obeechi
                

              Is this really a case where a track is on many albums? If not, then do/try this

               

              Album to many Tracks, where each are tables, and where a portal to the Track entity is on a layout based on the Album entity // like you already had set up originally

               

              Now create another table occurrence (or instance) of the Album entity, give it a unique name, and create a relationship between the unique IDs (you can use any field in this case, but unique IDs are good practice) of each of these two table occurrences based on the Album entity (the underlying table). This time, however, let the relationship operator be the X and not the =

               

              This allows you to see all albums at all times, and with more work you can create buttons or what-have-you to take you to different sets of tracks 

               

               

              • 4. Re: Relationship Link Problem
                Jade
                  

                Hi again and sorry for the delay,

                 

                I don't want to add to the confusion so if the tracks can only be on one album then follow Obeechi's advice.

                 

                If not, here are the answers to your questions: 


                1, Does the new table Album_Tracks require any records?


                 

                Yes, the join table will have one record for each combination of Album-track.  For example if Album number 25 has 3 tracks on it (numbers 12, 26, and 52), then the three records on the Album_Tracks will be: 25,12   25,26  and 25,52.  This links this Album to those three tracks.  Now if the first track (12) also happens to be on another Album (let's say number 103), then there will be another record: 103,12.  As you may see, we have linked three tracks to the first Album and one track to the second Album.

                 


                2, How does the link work as the album id will not be the same as the track id?


                 

                 Let's say you are working on the Album layout in Browse mode and you have a Portal of Album_Tracks on it.  When you add a new row to the portal (by selecting a Track number and name from a drop-down menu for instance), you are actually creating a new record on the Album_Tracks table.  Because of the relationships that you defined, FMP adds the Album_ID (e.g. 25) and the Track_ID (e.g. 12) in the record's fields.  This creates the link between the Album and the Track.  

                 

                Of course, I have again glossed over some details to simplify things for now. I will check back tomorrow morning to see where we are with this.

                 

                 

                P.S. This document is a good initial reference and explains things fairly well:

                 

                White Paper for FMP Novices

                http://www.foundationdbs.com/downloads.html 

                • 5. Re: Relationship Link Problem
                  Col_uk
                    

                  Thanks for your reply obeechi, if i can just explain the issue i have in more detail, for instance the track 2 minutes to midnight appears on the following albums, Edward the Great: Greatest Hits, Live After Death Disc 1, Powerslave, Rock in Rio Disc 1, Somewhere Back in Time: The Best of 1980-1989. so in my album table i will have all 5 albums created as records and in my tracks table i have 5 2 minutes to midnight records, so what i'm trying to acheive is a portal on my tracks table based on albums which will show every album each track appears on.

                   

                  I hope the above makes sense

                   

                  And thanks very much for your help

                   

                  Regards

                  Colin

                  • 6. Re: Relationship Link Problem
                    Col_uk
                      

                    Hi Jade

                     

                    Thanks for getting back to me, your comments are very useful and have know given me a greater understanding of what i need to do, i'll let you know how i get on.

                     

                    Thanks once again

                     

                    Regards

                    Colin

                    • 7. Re: Relationship Link Problem
                      Jade
                        

                      Hi Colin,

                       


                      Col_uk wrote:

                      …i have 5 2 minutes to midnight records…


                       

                       One of the major benefits of a relational database is that data need not be duplicated.  Only one track record for '2 minutes to midnight' is required and it can be linked to 5 different album records.

                       

                      Please don't hesitate to post back if you have a problem.  I continue to learn from and am amazed by the wealth of experience that the other contributors willingly share.


                      • 8. Re: Relationship Link Problem
                        comment_1
                          

                        Col_uk wrote:

                        the track 2 minutes to midnight appears on the following albums


                        I believe your terminolgy is a bit confusing. An album has tracks, and each track contains a recording (or performance) of a song. The same track CANNOT be on two albums. However, it's possible for track 2 of Album A to contain the same recording as track 6 of Album B.

                         

                        If you intend to reflect all this in your solution, you will need tables for Albums, Tracks, Recordings and Songs, arranged as:

                         

                        Albums -< Tracks >- Recordings >- Songs

                         

                         

                        Most people, I think, would ignore the fact that the recordings on some tracks are identical (they cannot tell for sure anyway, unless they have inside information), and settle for a simpler:

                         

                        Albums -< Tracks >- Songs