用户-项目关系的数据库设计
我正在制作一个销售小部件的网站。我有一个小部件表，每个小部件都有一些属性（小部件名称、样式等）。我还为用户准备了一张桌子。小部件具有唯一的 ID，用户也具有与其关联的唯一 ID。
我的用户想要开始向他们的帐户“添加”小部件。每个小部件都可以添加到无限数量的用户。所以我的问题是我应该制作一个表（我们称之为 User_Widget），将 UserID 与 WidgetID 相关联吗？
我想到的另一种方法是在用户表中有一列以逗号分隔的值（WidgetID1xQuantity、WidgetID2xQuantity 等）。如果我标准化这种格式，我可以使用 php 来解析它并将它们内爆到一个数组中，然后可以交叉引用该数组以从 Widget 表中提取信息。
哪一种练习更快/更好？如果我使用 User_Widget 方法，如果多个用户添加相同的小部件，会发生什么情况？我只有两个条目吗？这不会增加额外的开销吗？
小部件 |用户 宽度1 |用户1 宽度1 | user2
搜索这些（使用 JOIN 等）会比其他方法慢吗？
预先感谢您的帮助。
丹尼尔.
I'm making a website to sell widgets. I have a table for widgets, each of which have a few attributes (widget name, style, etc). I also have a table for users. Widgets have unique IDs, and users also have unique IDs associated with them.
My users want to start "adding" widgets to their account. Each widget can be added to an infinite amount of users. So my question is should I make a table, lets call it User_Widget, that relates UserIDs to WidgetIDs?
Another method I was thinking of is to have a column in the User table with comma separated values (WidgetID1xQuantity, WidgetID2xQuantity, etc). If I standardize this format, I can use php to parse this and implode these into an array, which can then be cross-referenced to pull info from Widget table.
Which one is faster/better practice? If I use the User_Widget method what happens if more than one user adds the same widget? Will I just have two entries? Does this not add extra overhead?
Widget | User
wid1 | user1
wid1 | user2
Would searching through these (using JOIN, etc) be slower than the other method?
Thanks in advance for the help.
Daniel.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我推荐带有关系的新表
它会工作得更快
I recommend new table with relations
It will works faster
绝对是特定表中的新关系。
除了前面评论中的其他缺点之外，请记住：
1）假设小部件有一些链接到它的信息，您仍然需要访问数据库来获取小部件数据，因此您实际上并没有节省太多（和其他人一样）指出，解析 CSV 很麻烦）
2）忘记做诸如“请列出所有在其小部件集合中具有 X 但没有 Y 和 Z 的用户”之类的事情 - 或者至少忘记有效地执行此操作。
3) 与“选择具有用户 Z 拥有的相同小部件的所有用户”相同
4) 如果您需要用 Widget Y 大规模替换 Widget X 该怎么办？或者给每个拥有 W 的人一个免费的 Z？
在很多场景中，专用表可以让您轻松描述 SQL 查询、效率、紧凑性。 CSV 字段“获胜”的情况很少（或没有）。
Definitely a new relation in a specific table.
Apart from the other drawbacks in the previous comments, remember that:
1) Assuming that the widget has some information linked to it, you still need to access the DB to get the widget data, so you are not actually saving much (and as others pointed out, parsing the CSV is cumbersome)
2) Forget doing stuff like "please list alla users having X but not Y and Z in their widget collection" - or at the very least forget doing it efficiently.
3) Same for "Select all users that have the same widgets owned by User Z"
4) What if you need to massively replace widget X with Widget Y? Or give everyone who has W a free Z?
There are plenty of scenarios where the dedicated table gives you easily described SQL queries, efficiency, compactness. And little (or no) cases where the CSV field "wins".