CODEWITHSUNDEEP
mysqldatabase
chowwy
chowwy

Reputation: 1126

store list of IDs mysql

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.

Upvotes: 1

Views: 4993

Answers (1)

tadman
tadman

Reputation: 211680

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.

Upvotes: 9

Related Questions