Many-to-Many

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Many-to-Many

Post by Luke »

I am having a hard time grasping this concept. I understand that it's like a book can have more than one author and an author can have more than one book... so you would have a table likethis for the author...

Code: Select all

Author_ID	Last Name	First Name
1		Jones		Mark
2		McDonald	Ronald
4		Smith		John
5		Doe		John
Another like this for the book...

Code: Select all

Book_ID		Book_Description	Book_Name
1		A book			The book
2		Some Book		Some book
3		The bookest book	Booky
What would the one that ties them together look like? I don't understand...
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post by hawleyjr »

The book table needs an author ID
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

That would be for a one to many wouldn't it? I need many to many.
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post by hawleyjr »

I see, you will need a third table.

Code: Select all

unique id | Book ID | author ID
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post by Ambush Commander »

Ooh, ooh, PoEAA!
Association Table Mapping

Objects can handle multivalued fields quite easily by using collections as field values. Relational databases don't have this feature and are constrained to single-valued fields only. When you're mapping a one-to-many association you can handle this using Foreign Key Mapping, but a many-to-many association can't do this because there is no single-valued end to hold the foreign key.

The answer is a classic resolution that's been used by relational data people for decades: create an extra table to record the relationship.
Google the term.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

Thank you both!

EDIT: I will be returning to this topic when I need to pull information from the DB because I don't yet understand joins and I will need to, correct?

DOUBLE EDIT: I used books as an example, but what I really need this for is another directory. I have one table with businesses, one table with categories, and now I will need to connect the two with another table...
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

In a relational model, you can't represent n-m relationships directly.. They are handled by introducing a "link" table so that you end up with two 1-n relationships (as hawleyjr suggested).

You don't need to know about joins (simply selecting from a, b gives you the product too) but i can only recommend you to learn what the different kind of joins are (inner, outer, natural, ...)

Offcourse, you can also add "extra" information to the link table. Eg: You have a collection of teachers and a collection of students (A teacher teaches to multiple students and a student can be teached by multiple teachers, so n-m relationship). You may also want to know when this teaching is going on.. So you end up with tables like:

teacher (teacher_id, ... )
student (student_id, ... )
subscription (teacher_id, student_id, startdate, enddate, ... )
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

OK, I have the tables set up and now I am setting up the admin panel for the new directory...
Table One - Listings:

Listing_id, listing_name, listing_description

Table Two - Categories:

Categories_id, categories_name

Table Three - Listings-Categories:

Unique_id, Listings_id, Categories_id

Now I need to select all from listings where the category id is 23

What would this query look like? Does this involve joins? Any kind of advice would be VERY helpful.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

hawleyjr wrote:I see, you will need a third table.

Code: Select all

unique id | Book ID | author ID
All of that makes perfect sense to me...
A table for book information, a table for author information, and a table that contains information their relationship(s)

I have my database tables set up that way... now how do I select just listings with a certain category??
Last edited by Luke on Wed Dec 28, 2005 6:42 pm, edited 1 time in total.
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post by hawleyjr »

Code: Select all

//UNTESTED
SELECT a.Categories_id, a.Listings_id,a.Unique_id,b.listing_name,b.listing_description, c.categories_name 
FROM Categories as a
LEFT JOIN Listings as b on a.Listings_id = b.Listing_id 
LEFT JOIN Categories as c on a.Categories_id = c.Category_id
where a.Category_id = 23
Edit: Cleaned up the SQL. Fixed one parse error.
Last edited by hawleyjr on Wed Dec 28, 2005 6:45 pm, edited 1 time in total.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

I will try my hardest to make sense of that... thank you.

let me get this straight

table a: relationship table
table b: listing table
table c: category table

Right?


Where did a, b and c come from??
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

Wouldn't this work?

SELECT dircat.directory_id
FROM dircat, directory
WHERE dircat.directory_id = directory.directory_id
AND dircat.cat_id =1
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

You already know the answer no? Might want to think about the difference between a select from 2 tables and a select with a join...
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

Not sure I understand what you just said.
Post Reply