SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2003
    Location
    Sacramento
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Many-Many Design Question (should Lookup Table have > 2 columns?)...

    Should my many-to-many "lookup table" have more than 2 fields...?

    myLibrary example: books-patrons relationship

    1. Each book may be checked out by *multiple patrons.*
    2. Each patron may check out *multiple books.* [Indeed, each patron may check out each book *multiple times*...but that's another matter (outside of this msg's scope). ]
    3. BUT...each book may also be reviewed by each patron. (In my scheme, only *one* review may be offered by each patron for each book!)

    My "lookup table" -- tblBooksBorrowed -- contains COLUMNs (fields):
    bID
    pID

    Is it good to add the COLUMN...
    review
    ...to the "lookup table"?

    OR, should I slice it off to another mini-table (but why would I do that -- efficiency in some cases)? I can't see the need. But I recognize that my "lookup table" is becoming more than just a "lookup."

    AGAIN, *only one* review per book per patron.

    Thanks much, Barry

    P.S. I'm also considering adding a *Rating* System. Then I'll have to decide whether to add COLUMN ratingValue to my "lookup table." Any thoughts?

  2. #2
    ☆★☆★ silver trophy vgarcia's Avatar
    Join Date
    Jan 2002
    Location
    in transition
    Posts
    21,235
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    I would add "review" to the lookup table. If you made a separate "review" table, you'd have to add the bookID and personID columns in there too, causing unnecessary redundancy. You can also add the rating column to your lookup table, since only one rating can be given per review (I'm assuming, right?).

    Hope this helps!

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    If you made a separate "review" table, you'd have to add the bookID and personID columns in there too, causing unnecessary redundancy
    actually, it isn't redundant, and it is necessary

    if it were in the same table, how would you review a book without borrowing it? how would you borrow a book multiple times? (i know poly said this was out of scope, but it isn't)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •