1

I want to store a list of book IDs for a wishlist in mysql; here's what the relevant tables look like:

User_Wishlists
user_id, list_title, list_id (PK)

Wishlist
list_id (FK), book_id (PK)

The issue is with the book_ID. Most users will have multiple books on their lists, but it seems strange to have that represented by book1_id, book2_id, etc. in the table. Is there a better way to store a list of these book_IDs in mysql? I need to be able to retrieve and display a user's wishlist for them.

chowwy
  • 1,126
  • 8
  • 26
  • 45

1 Answers1

8

You need to have an association table that joins users to wishlists. Remember the zero, one, or N principle of database design. Either you have no relationship, a one to one, or a one to many. Using things like book1_id is almost always a defective design.

You may find this is a better structure:

Wishlist
wishlist_id, user_id, ...

WishlistEntry
wishlist_id, book_id, ...

Associate the wishlist with the user directly, then use the WishlistEnry table to find which books are on which wishlist.

tadman
  • 208,517
  • 23
  • 234
  • 262
  • 1
    This is a good solution. I'm new to database design and after using several tutorials, I instinctively knew the book1, book2 wasn't the best answer. I hadn't thought of a separate table just for the entries, which is perfect--a 1 to many solution. Answer accepted and upvoted. – chowwy Jan 05 '12 at 19:53
  • Half of finding the right solution is knowing you have the wrong one! – tadman Jan 05 '12 at 20:04