Results 1 to 3 of 3
Jun 20, 2003, 15:12 #1
- Join Date
- Jun 2003
- 0 Post(s)
- 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):
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?
Jun 20, 2003, 16:40 #2
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!
Jun 20, 2003, 20:17 #3
If you made a separate "review" table, you'd have to add the bookID and personID columns in there too, causing unnecessary redundancy
- Join Date
- Jul 2002
- Toronto, Canada
- 58 Post(s)
- 3 Thread(s)
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)