[thelist] sql/table design question

Jacques Capesius jacques_capesius at cnt.com
Tue Feb 18 09:45:01 CST 2003


> I have a table that lists a bunch of books. Parts of these books are
> reprinted in other books, which are in the same table. For example:
>
> Book#1 (parts reprinted in Book#34 and Book#47)
>
> So when I list out all the specs for book#1, I want to reference the
> two other books as well, and create a link to them should the person
> want to find out the information on those as well.
>
> Is this going to create a problem for me with my select statements?
>
> Here is my table layout:
>
> bookID
> bookTitle
> bookNotes
> bookRelatedTo (here is where I would list the bookIDs that match)
>

Personally, I would set things up kinda like this, tho I have about as much
SQL skill in my whole body as Rudy has in half a hair follicle.
Nonetheless...

table1: T_BOOK
--------------------
bookID (primary key)
bookTitle
bookNotes

table2: T_BOOK_RELATION
------------------------
bookID
related_bookID

This way, you can have a many to many relation between the books. I assume
that a book will be related to potentially many books, and a book can have
many books related to it. To handle such things, you would need a structure
set up like the above.

With this setup, a SQL Statement that would pull the books related to a book
titled, say "Moby Dick" would run something like this:

SELECT A.Title 'title', C.Title 'related titles'
FROM T_BOOK A, T_BOOK_RELATION B, T_BOOK C
WHERE A.Title = 'Moby Dick'
AND A.bookID = B.bookID
AND B.related_bookID = C.bookID
ORDER BY 2

HTH.

-jacques :)



More information about the thelist mailing list