我想设计一些允许用户将清单放在一起的东西，比如杂货。 如果每个用户可以有多个列表（即不是设定数量），并且这些列表上都有可变数量的项目（同样，未设置），而某些项目出现在几个不同的列表中 ，如何创建数据库而不会非常可怕多余？
我对这类问题完全不熟悉，以前没有把任何复杂的数据库放在一起，也不知道从哪里开始。 这就是我想出的一个例子，但我怀疑这是正确的做事方式：
任何帮助或想法将不胜感激！
I'd like to design something that would allow users to put lists together of, let's say, grocery items. If each user can have multiple lists (i.e. not a set amount) and those lists all have a variable number of items on them (again, not set) while some items appear on several different lists, how do I create a database without being horribly redundant?
I'm completely new to this kind of problem, not having put together any complex database before, and have no idea where to start. This is what I came up with as an example, but I doubt this is the right way of doing things:
Any help or ideas would be much appreciated!
最满意答案
看起来你需要类似这样的东西：
列表对用户是私有的，但是项目可以在多个列表之间共享：
USER和LIST之间的关系是一对多，它通过一个简单的外键建模。 LIST和ITEM之间的关系是多对多的，它由它们之间的联结（aka。链接）表建模：LIST_ITEM。我在上图中使用了USER和LIST之间的识别关系，在“下游”表中产生了更多“自然”键：
减少对JOIN的需求（您已经知道给定LIST_ITEM属于哪个USER，而无需使用LIST加入）。 但使下游密钥“更胖”。使用USER和LIST之间的非标识关系（生成“slimmer”键）的设计如下所示：
Looks like you need something similar to this:
A list is private to user, but an item can be shared among multiple lists:
The relationship between USER and LIST is one-to-many which is modeled through a simple foreign key. The relationship between LIST and ITEM is many-to-many, which is modeled by a junction (aka. link) table in between them: LIST_ITEM.I have used identifying relationship between USER and LIST in the diagram above, producing more "natural" keys in the "downstream" tables which:
Reducing the need for JOINs (you already know which USER a given LIST_ITEM belongs to, without the need to JOIN with LIST). But makes downstream keys "fatter".The design using non-identifying relationship between USER and LIST (producing "slimmer" keys) would look like this:
更多推荐
发布评论