List: General Discussion « Previous MessageNext Message » From: Claudio Nanni Date: February 22 2009 10:31pm Subject: Re: book categories View as plain text
Hi Phil,
relax you are on track!
First a little theory.
In this case you have the so called N-to-M relationship.
While if you need to relate one book to one author(given only one author per
book!)
you have the so called 1-to-N relationship, in this case you have the N-to-M
relationship.
If you are asking yourself why one book-one author is a 1-to-N relationship
do not think of the single row/record,
but reason this way(again, given only one author per book):

Take one row on the [books] table and ask yourself: "How many possible
authors have written this book?"  Ans: 1
Take one row on the [authors] table and ask yourself: "How many possible
books have been written by this authors" Ans: Many (N any number)

so you have a 1-to-N relationship

in your example you have [books] and [categories]

Take one row on the [books] table and ask yourself: "To how many possible
categories belongs this book?"  Ans: Many (N)
Take one row on the [categories] table and ask yourself: "How many books can
belong to this category?" Ans: Many (M , N is already taken!)

so you have a N-to-M relationship

while to implement a 1-to-N relationship you use a Foreign Key on one table,
to implement a N-to-M relationship you need to use a table (cross reference)

This means that while you still have the two original tables you will build
a third table that connect the former two ones.
This third table(Cross reference) it is just composed, at least, by two
fields(two foreign keys) one referring to the primary key
of the first table [books] and one referring to the primary kay of the
second table [categories].
Just insert for each book as many rows as the categories to which the book
belongs, for example,
if you have a book with book_id=1 and various
categories(1,2,3,4,5,6,7,8,9,10), to assign to the book
the categories 1,3,8 insert three rows in the cross-reference table:

book_id | category_id
-------------------------------
1           |       1
1           |       3
1           |       8

AGAIN: CONCAT is just a string function, it is not a sql operator, and it is
not giving anything that you don't already know,
dont focus on CONCAT, forget it for now.

Let me know

Claudio Nanni

2009/2/22 PJ <af.gourmet@stripped>

> Here's a tough one...
> In my library I have some 62 categories where a number of books can be
> in several categories.
> Now, to relate the "categories" table(AS c) to the "book" table (AS a),
> I assume that I need an intermediate table "book_category" (AS d)
> linking d.bookID to the b.bookID and d.categoryID to c.id. With so many
> categories and (probably) thousands of books d may become quite lengthy
> (but, I guess it's better than 62 * 100's of lengthy fields added to b).
> To enter the relationships I would add instructions on my
> addNewBooks.php form with the input as multiple choice dropdown box
> (listing the categories)...
> so far, so good (I hope)...
> Now, how do I SELECT and retrieve these categories to display on the web
> page?
> From the book_category table with a WHERE statement? If so, what then?
> CONCAT_WS the stuff to go into the html table <td>?
> I hope I'm on the right track... or am I in deep water?
> Help...
>
> --
>
> Phil Jourdan --- pj@stripped
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
>
>