[lug] Learning about databases

[George Sexton]

You are on the right track.

Given:

Author
___________
Author_ID
Name

Book
___________
Book_ID
Author_ID
Title

and

Genre
________
Genre_ID
Description

The best way to do the relationship is with a table.

Book_Genre
____________
Book_ID
Genre_ID

In this manner each book can have an unlimited number of Genres assigned.
Further you would want:

Book
________
Book_ID
Title

BookAuthor
___________
Book_ID    -\Composite Primary Key
Author_ID  -/

Books non-fiction books frequently have multiple authors..

It looks like a lot of overhead to have this additional table but if you use
a 4 byte integer as the primary and foreign keys, the amount of disk space
used is usually very trivial. If, for example, you had 1 million rows with
an average of 1.5 genres each, the table would consume roughly 12MB of
space. Not even worth worrying over. You cannot have "Genre_1, Genre_2,
Genre_3" for two reasons: a) it violates first normal form and b) any query
that tests for a specific value (or join) would have to join on each of the
3 fields. This is very bad.


George Sexton
MH Software, Inc.
Voice: 303 438 9585
Fax: 303 469 9679
URL: http://www.mhsoftware.com

-----Original Message-----
From: lug-admin at lug.boulder.co.us [mailto:lug-admin at lug.boulder.co.us]On
Behalf Of socket at peakpeak.com
Sent: Thursday, January 06, 2000 2:09 PM
To: lug at lug.boulder.co.us
Subject: [lug] Learning about databases


Hi, everyone.

In the time I've got before spring semester starts, I'm trying to
learn something about relational databases-- it's the most significant
gap in my computer knowledge these days.  I dropped by Barnes and
Noble, browsed through some SQL books and the O'Reilly MySQL and mSQL
book, and am still a little fuzzy on some points.  Perhaps someone
could point me to a good online tutorial and reference?

Here's the issue: it seems that a lot of relationships I'd want to
represent aren't as simple as a one-to-many relationship. I've got a
library of books, for example, and might want to represent the genres
in each book.  It seems like it'd be pretty simple: the book table
would have a key into a genre table.  But a book might have more than
one genre-- it could be both mystery and sci-fi.  And turning the
relationship around, one genre contains many books, but two genres
could overlap.

I was thinking this would be represented easiest with intermediate
tables.  A book would point to the table that represents its specific
combination of genres, which would have a key pointing to a genre list
table.  But that would involve a table containing nothing but primary
and foreign keys, which seems a huge waste...  I have a number
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?

--
Chris Riddoch                  socket at peakpeak.com
Will provide pseudo-insightful commentary for food
                   http://www.peakpeak.com/~socket

GPG key 1024D/234551DC 1999-07-15 Chris Riddoch <socket at peakpeak.com>
Key fingerprint = 7AAF 5815 837C 070C 6C70  8A15 EFD2 5860 2345 51DC

_______________________________________________
Web Page:  http://lug.boulder.co.us
Mailing List: http://lists.lug.boulder.co.us/mailman/listinfo/lug