MySQL数据库设计问题

Question

情况
假设我有以下内容：

• 一个名为items的表格
• 一个名为lists的表格，可以将这些项目链接到其中
• 一个名为users的表格

条件：列表可以属于所有或一个特定的用户

我想做的是创建存在于每个用户中的“全局”列表。但应该有可能使用用户特定的列表来“覆盖”此全局列表。例如对于随机用户：

• 一个名为books的全局列表
• 一个名为cds的用户特定列表

现在，当检索此用户的列表时，我应该获得全局书籍列表和用户特定的CD列表。但是，如果此用户有特定的书籍列表呢？

• 一个名为books的全局列表
• 一个名为books的用户特定列表
• 一个名为cds的用户特定列表

因此，现在它应该检索名为books的用户特定列表，而不是全局列表，当然还有用户特定的cds列表。

可能的解决方案
最简单的方法是：

table: lists
- id
- name
- userId

如果userId IS NULL ，则意味着它是全局列表，但当它不为 NULL 时，则属于特定用户。因此，第一个示例将是userId = 1：

id  name    userId
1   books   NULL
2   cds     1

要检索此用户的列表：

SELECT * FROM lists WHERE userId = 1 OR userId IS NULL

但是，然后我卡在下一部分：

id  name    userId
1   books   NULL
2   cds     1
3   books   1

这里的第一个列表“books”是全局列表，但是此用户还有一个用户特定列表“books”。是否可以忽略此情况中的全局列表，并仅返回用户特定列表？

另一个可能性是创建一个lists_users表格，将所有用户链接到所有全局列表，并在创建具有与其中一个全局列表相同名称的用户特定列表时删除此链接。虽然这似乎不太高效，但添加新用户时需要更多的工作来维护。

问题:
在数据库中设置这个最佳且最有效的方式是什么，以便我可以轻松地为特定用户检索正确的列表？

- Alec

Answer 1

对于您当前的设计，您可以通过以下方式获取用户列表：

SELECT * FROM lists 
WHERE userId = 1 
UNION ALL
Select * from lists g 
Where userId IS NULL
AND NOT EXISTS (SELECT 1 from lists l where g.name = l.name and userid = 1)

即选择用户特定的列表和全局未被用户重新定义的内容

- Michael Pakhantsov

Answer 2

如果您过滤项目而不是列表会怎样呢？

Select distinct   -- items from user-specific lists 
      ItemName
    , ItemType
from User     as u
join UserList as x on x.UserId = u.UserId
join List     as t on t.ListId = x.ListId
join ListItem as y on y.ListId = t.ListId
join Item     as m on m.ItemId = t.ItemId
where ListType = 'custom'
and u.UserId   = some_user_id
UNION
Select distinct 
      ItemName  -- items from global lists
    , ItemType
from List     as t on t.ListId = x.ListId
join ListItem as y on y.ListId = t.ListId
join Item     as m on m.ItemId = t.ItemId
where ListType = 'global'

order by ItemType, ItemName
;

- Damir Sudarevic

Answer 3

在我个人看来，当你开始在数据库中使用NULL值来解决概念问题时，这意味着你需要更改你的表结构。

我建议采用以下方法：（对于混乱的UML图表示抱歉）

这个想法的背后是，你可以使用“遗产”来区分两种类型的列表：UserDefinedList和DefaultLists。正如你所看到的，只有UserDefinedLists与用户相关，而DefaultLists则对任何想要使用它们的用户开放。当然，当有人想创建一个名为books的列表时，你可以告诉用户该列表已经创建，但实际上你不会将任何内容插入数据库。

当你想要显示用户的列表时，你会显示UserDefinedLists和所有的DefaultLists。

可能听起来有些混乱，但故事的寓意是，当你有不同类型的元素（如列表），每种类型与其他表格有不同的关系时（即，UserDefinedLists与用户相关，而DefaultLists则不相关，但它们都是列表的一种类型时），遗产在数据库中非常有用。

希望这有所帮助。

- pleasedontbelong