Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Many to Many Relations in Database

Tags:
  1. May 12, 2015 #1

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    I am pulling my hair trying to find a straight answer to this, after looking it up in
    different books, websites:
    Say we have a many-to-many relationship in a RDB (Relational DB). Is there a standard
    way of creating a junction, bridge, etc. table? From what I know,. the bridge table will contain
    the primary keys of both table. Will the bridge table contain _exactly_ these two fields, or does
    this depend on the particulars of the databases involved?

    I would also appreciate some help in this: once we have a junction table. Say we want to
    conduct a query between the two tables that are in many-to-many relationships. How do we then
    conduct a query, do we do it on the join of one of the many-many tables and the junction table?

    Thanks.
     
    Last edited: May 12, 2015
  2. jcsd
  3. May 13, 2015 #2

    Svein

    User Avatar
    Science Advisor

    I would usually just use those two fields.
    That depends. If your junction table just contain the primary keys, setting up a query would usually involve some knowledge outside the junction table. I usually set up a query using all tables and then specify what records I want from one table. The query would then return all the relevant records from the other table.
     
  4. May 13, 2015 #3

    jtbell

    User Avatar

    Staff: Mentor

    In my database for my classical-music collection, I have a many-to-many relationship between "performer" and "performance", implemented with a junction table "performance_performer" that contains only the numeric IDs (which I use as primary keys) from both tables. A simple query for all performances by a specific performer might look like this:

    Code (Text):

    select performance.description
    from performance, performer, performance_performer
    where performer.lastname = "Heifetz"
    and performance_performer.performerid = performer.id
    and performance.id = performance_performer.performanceid;
     
     
  5. May 13, 2015 #4

    Svein

    User Avatar
    Science Advisor

    I also have a music collection database. In my case I have a table named "Track" that links to several tables: Album, Composer and Performer. In addition, it contains the track number and the track title.
     
  6. May 13, 2015 #5

    jtbell

    User Avatar

    Staff: Mentor

    In your case, "track" is a physical entity which also can be used to relate other entities. In my case, "performance_performer" is a purely conceptual entity that expresses a many-to-many relationship. A single performance can have more than one performer taking part in it (e.g. soloist(s) and conductor for a concerto), and a single performer can appear in many different performances (usually of different compositions).
     
  7. May 13, 2015 #6

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    Thanks, all.
     
  8. May 14, 2015 #7

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    I was told today that a bridge/junction table should include, together with both primary keys, any field that is functionally-dependent on those two keys. I think the argument is that not including it would violate some normalization; I think the third.
     
    Last edited: May 14, 2015
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook




Similar Discussions: Many to Many Relations in Database
Loading...