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...
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.
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...
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:
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.
//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.