Page 1 of 2
Many-to-Many
Posted: Wed Dec 28, 2005 12:57 pm
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...
Posted: Wed Dec 28, 2005 1:04 pm
by hawleyjr
The book table needs an author ID
Posted: Wed Dec 28, 2005 1:09 pm
by Luke
That would be for a one to many wouldn't it? I need many to many.
Posted: Wed Dec 28, 2005 1:15 pm
by hawleyjr
I see, you will need a third table.
Posted: Wed Dec 28, 2005 1:18 pm
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.
Posted: Wed Dec 28, 2005 1:20 pm
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...
Posted: Wed Dec 28, 2005 1:46 pm
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, ... )
Posted: Wed Dec 28, 2005 4:27 pm
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.
Posted: Wed Dec 28, 2005 6:37 pm
by Luke
hawleyjr wrote:I see, you will need a third table.
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??
Posted: Wed Dec 28, 2005 6:41 pm
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.
Posted: Wed Dec 28, 2005 6:43 pm
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??
Posted: Wed Dec 28, 2005 7:01 pm
by timvw
Posted: Thu Dec 29, 2005 10:55 am
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
Posted: Thu Dec 29, 2005 4:33 pm
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...
Posted: Thu Dec 29, 2005 5:03 pm
by Luke
Not sure I understand what you just said.