[lug] Learning about databases

[Calvin Dodge]

socket at peakpeak.com wrote:
> 
> relationships of this sort that I'd like to represent.  The best way I
> can describe this is a many-to-many relationship, and I'm a little
> lost on where to go now.
> 
> What's the appropriate way to represent such relationships (keywords,
> genres, etc.) with mysql?

For a many-to-many relationship you use three tables (with _any_ database):

Books (which include a primary ID)
Genres (also includes a primary ID)
Intermediate table (contains a link to a single book and a link to a single
genre)

So you could have

BookID | title
   3     "The Mouse On The Moon"

GenreID | type
   2      "satire"
   3      "western"
   4      "science fiction"

then your intermediate table would look like this:

booklink | genrelink
   3          2
   3          4

Which would indicate that "The Mouse On The Moon" is both "satire" _and_
"science fiction".

Calvin

-- 
Calvin Dodge
Certified Linux Bigot
http://www.caldodge.fpcc.net