• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Many to many relationships (sql)

 
Ranch Hand
Posts: 185
Netbeans IDE Firefox Browser Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

I am not sure if this should be posted here but does anyone have any good explanations (or links) as to how to use joined tables from many-to-many relationships in a program? Say I have an actor table and a film table, each containing details of each. Now its my understanding that a film can have many actors and an actor can have many films, and the solution to map many-to-many relations is to create a one-to-many table between the two containing foreign keys to both. If I have a select query to select all films that had a certain actor in it how would I go about using this table? ie. what is the point of this table and how do I use it?

Thanks,
Alan
 
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
treat it the same as any other join in a table, do you know how to do joins in SQL?
 
Alan Smith
Ranch Hand
Posts: 185
Netbeans IDE Firefox Browser Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Wendy Gibbons wrote:treat it the same as any other join in a table, do you know how to do joins in SQL?



Hi Wendy, I will have a look at joins. Composite keys came up as well so I will look at those too. Thanks for the tip.
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Alan Smith wrote:what is the point of this table and how do I use it?


This would be an "intersection table" and is used simply to provide a mapping between the primary keys of the two tables at either end of the M:N relationship. Normally, a parent-child key relationship is 1:N, and you would include the primary key of the parent (the "1" end) in the child table (the "N" end), where it is called a "foreign key" back to the parent table. So you can find the children for a given parent, or join back to the parent from the children if needed. But when you have a M:N (many-to-many) relationship, this won't work, because there is no "1 end" of the relationship. So you add the intersection table, which allows you to turn the "M:N" relationship into two separate relationships - 1:M from one parent, and N:1 to the other parent - where your intersection table holds foreign keys back to each of the parents. The records in the intersection table actually represent this M:N relationship in a normalised form, and the primary key of the intersection table is the combination of the two foreign keys.

For example, a book can have several authors, and an author can write many books, so Author:Book is a M:N relationship. Assume your BOOKS table has a unique book_id as its primary key, plus a title and various other attributes. The AUTHORS table has a unique author_id as its PK, plus a name and other attributes. The intersection table might be called BOOK_AUTHORS and would simply contain the two PKs with one row for each of the required book/author combinations. So the book "100 Great Widgets" by Quentin Zyzygy and Amanda Aardvark would be represented like this:


This table structure allows each book to have several authors, and each author to have many books, which is exactly what we need.

As Wendy says, once you have set this up, you can use it in the same way as any other table by joining to/from the table in SQL etc. ORMs and other non-relational technology may have different conventions for managing M:N relationships, but it's still the same principle in your relational database underneath.
 
Alan Smith
Ranch Hand
Posts: 185
Netbeans IDE Firefox Browser Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

chris webster wrote:

Alan Smith wrote:what is the point of this table and how do I use it?


This would be an "intersection table" and is used simply to provide a mapping between the primary keys of the two tables at either end of the M:N relationship. Normally, a parent-child key relationship is 1:N, and you would include the primary key of the parent (the "1" end) in the child table (the "N" end), where it is called a "foreign key" back to the parent table. So you can find the children for a given parent, or join back to the parent from the children if needed. But when you have a M:N (many-to-many) relationship, this won't work, because there is no "1 end" of the relationship. So you add the intersection table, which allows you to turn the "M:N" relationship into two separate relationships - 1:M from one parent, and N:1 to the other parent - where your intersection table holds foreign keys back to each of the parents. The records in the intersection table actually represent this M:N relationship in a normalised form, and the primary key of the intersection table is the combination of the two foreign keys.

For example, a book can have several authors, and an author can write many books, so Author:Book is a M:N relationship. Assume your BOOKS table has a unique book_id as its primary key, plus a title and various other attributes. The AUTHORS table has a unique author_id as its PK, plus a name and other attributes. The intersection table might be called BOOK_AUTHORS and would simply contain the two PKs with one row for each of the required book/author combinations. So the book "100 Great Widgets" by Quentin Zyzygy and Amanda Aardvark would be represented like this:


This table structure allows each book to have several authors, and each author to have many books, which is exactly what we need.

As Wendy says, once you have set this up, you can use it in the same way as any other table by joining to/from the table in SQL etc. ORMs and other non-relational technology may have different conventions for managing M:N relationships, but it's still the same principle in your relational database underneath.



Great explanation, thank you! I'm guessing all the 'pairs' of keys in the intersection table have to be unique as opposed to the individual keys being unique?
 
chris webster
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Alan Smith wrote:I'm guessing all the 'pairs' of keys in the intersection table have to be unique as opposed to the individual keys being unique?


Exactly: it's the combination of Book + Author, for example, that is unique in this table.
 
reply
    Bookmark Topic Watch Topic
  • New Topic