6 Replies Latest reply on Apr 6, 2017 12:40 PM by sejax422

    Trouble Finding a common field to link tables


      I have a database with a few tables to keep track of scene continuity for a feature film.  I'd like to be able to link my "Continuity" table to a separate table called "Music Tracker."  I'm not sure exactly how to link them but here is what my goal is:


      Table 1 is "Scene Continuity" - each record represents one scene.

      (FIELDS: scene number, location, scene description, song title)


      Table 2 is "Music Tracker" - each record represents a series of scenes.

      (FIELDS: cue number, song title, artist, scenes used [a song could span 5 scenes])


      I understand portals enough that I can create a portal in my Music Tracker to indicate all the scenes in which the song is being used in the Continuity table.  That is, if my process is to update the Song Title field in the Continuity table first.  I'd rather not do this.  Here's why:


      Because of how often songs change during the edit, It's much simpler to update my Music Tracker table first.  As a song changes, I'd like it to link directly to the corresponding records (scenes) in the Continuity table.  Thus, if I update "SONG A" in the Music Tracker table and the Songs Used field = "1-5", then "SONG A" will auto-populate into the Continuity table under the Song Title field for records (scenes) 1-5.


      How then, can I go about doing this, if each record in the Music Tracker table represents a series of scenes, rather than one scene? 


      How do I link the records in the Music Tracker  to Continuity if the "Scenes Used" field = 1-5 and in the Continuity table the "Scene Number" field = "1," "2," "3," "4," and "5" separately?  Is there a calculation I can use?  Do I need to create a proxy field for linking?


      Many thanks in advance for your help!

        • 1. Re: Trouble Finding a common field to link tables

          It sounds like the classic use case for a 'join' table that sits between the scenes and the music.  Each record in that table would represent a track for one scene.  Multiple records for the same track indicate that it is used in more than one scene.

          1 of 1 people found this helpful
          • 2. Re: Trouble Finding a common field to link tables

            Wim's call is spot on.. you definately need a join table.


            As far as "link" fields each table should have a primary key. A field whos value is never empty, is unique for all records in the table, has no real meaning to the data, and is not ever editable by the user. Auto increment serial number is an option as is get UUID.

            A join table should have its primary key field and two foreign key fields that house the values from the related tables primary key field.




            See attached example

            • 3. Re: Trouble Finding a common field to link tables

              I will disagree. Unless you need to link more than one song to the same scene, you do not need a join table. And even if you do need a join table, it does not solve the update problem created by changing a song title.


              The solution to that is simple. Don't link your records by the name of the song. Define a field in Music Tracker that is either a number field that auto-enters a serial number or a text field that auto-enters get(UUID). Use this field to link a song to either a scene record or, if you really need it, a join table record . Now you can edit the song title whenever needed and your scenes remain linked to the new song.


              This is an example of why you should not use names as match fields in relationships.


              PS: value lists can be set up for selecting a song by name that enter the ID. Other selection widgets that do this are also possible.

              • 4. Re: Trouble Finding a common field to link tables

                Thanks philmodjunk!  But what fields am I linking the serial number to?  So, in Music Tracker, if I create an ID number for each song, "SONG_ID" and each song has an ID number of "SONG_001," "SONG_002," etc, what field in my Scene Continuity am I linking to so that it matches?  Do I have to create another ID field in Scene Continuity so that they connect somehow?

                • 5. Re: Trouble Finding a common field to link tables

                  Yes, you need another ID field, but PLEASE do NOT use "Song_001" as an ID. Use just a serial number, 1, 2 , 3 ... or a UUID entered into a text field for your primary key. Don't complicate an ID field that need never be visible to the user on any of your layouts with additional data and formatting requirements. Keep it as simple and straight forward an ID as possible.


                  You can use your current relationship that matches by name to assign the new ID's to your existing continuity records before you then update your relationships to match by the new field.

                  • 6. Re: Trouble Finding a common field to link tables

                    Thanks Everyone.  I was able to successfully link Songs to Scenes by using a join table.  Maybe there's another way as suggested above that I didn't need a join table, but I was able to understand how to use a Join Table and it's working so far for what I need.


                    Thanks again!