存储mysql的列表 [英] store list of IDs mysql

问题描述

我想在mysql中存储一个愿望清单的书籍ID列表;以下是相关表格的样子：

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）

User_Wishlists
user_id, list_title, list_id (PK)

收藏

list_id（FK），book_id（PK）

Wishlist
list_id (FK), book_id (PK)

问题在于book_ID。大多数用户在其列表中将有多本图书，但是在表中由book1_id，book2_id等表示的似乎很奇怪。有没有更好的方法来存储这些book_IDs在mysql的列表？我需要能够检索并显示用户的愿望清单。

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.

推荐答案

您需要一个关联表到愿望清单。记住数据库设计的零，一或N原则。或者你没有关系，一对一，或一对多。使用 book1_id 几乎总是有缺陷的设计。

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.

您可能会发现这是一个更好的结构： p>

You may find this is a better structure:

Wishlist
wishlist_id, user_id, ...

WishlistEntry
wishlist_id, book_id, ...

将wishlist与用户直接关联，然后使用WishlistEnry表来查找哪些图书是哪张心愿单。

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

这篇关于存储mysql的列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！