Many to many relationships (sql)

[Alan Smith]

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 L Gibbons]

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

[Alan Smith]

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]

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:

BOOKS table
=======
Book id:1, Title: "100 Great Widgets"

AUTHORS table
=========
Author id: 100,  Name: Quentin Zyzygy
Author id: 200, Name: Amanda Aardvark

BOOK_AUTHORS  table (PK = combination of book_id  and author_id)
============
Book id: 1, Author id: 100
Book id: 1, Author id: 200

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]

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:

BOOKS table
=======
Book id:1, Title: "100 Great Widgets"

AUTHORS table
=========
Author id: 100,  Name: Quentin Zyzygy
Author id: 200, Name: Amanda Aardvark

BOOK_AUTHORS  table (PK = combination of book_id  and author_id)
============
Book id: 1, Author id: 100
Book id: 1, Author id: 200

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]

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.