aspose file tools*
The moose likes JDBC and the fly likes Many to many relationships (sql) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Many to many relationships (sql)" Watch "Many to many relationships (sql)" New topic
Author

Many to many relationships (sql)

Alan Smith
Ranch Hand

Joined: Oct 19, 2011
Posts: 162

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
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

treat it the same as any other join in a table, do you know how to do joins in SQL?
Alan Smith
Ranch Hand

Joined: Oct 19, 2011
Posts: 162

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.
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1775
    
  14

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.


No more Blub for me, thank you, Vicar.
Alan Smith
Ranch Hand

Joined: Oct 19, 2011
Posts: 162

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

Joined: Mar 01, 2009
Posts: 1775
    
  14

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Many to many relationships (sql)