数据库设计：用户可以是许多列表的一部分，列表可以与多个用户相关联

So I'm trying to work on a small app that would allow you to have a grocery list that can be edited by multiple people via their mobile phone. So I'm trying to design the database I will use on the backend. I'm having trouble figuring out how to relate all the tables. User will be able to be apart of multiple lists, and each list will be accessible by multiple people. ( I believe this is called a many-to-many relationship?) So far my tables look as follows:

所以我正在努力开发一个小应用程序，它可以让你有一个可以由多人通过他们的手机编辑的购物清单。所以我正在尝试设计我将在后端使用的数据库。我无法弄清楚如何将所有表格联系起来。用户可以分成多个列表，每个列表可供多人访问。（我相信这被称为多对多关系？）到目前为止，我的表格如下：

User(id, name)
List(id, Name)
List_item(id, List_id(key), name, amount)

The list to list_item ID relationship is easy enough, but how do I go about modelling the relationship between Users and Lists? Do I need a separate join table:

list_item ID关系列表很容易，但我如何建模用户和列表之间的关系？我需要一个单独的连接表：

User-to-List(User ID,List ID)

I'm trying to come up with the most efficient structure as possible, Thanks!

我想尽可能地提出最有效的结构，谢谢！

Edit: Only the user needs to know about all the lists he is associated with, the list doesn't need to keep track which user are associated with it. At least for the initial app!

编辑：只有用户需要知道与之关联的所有列表，列表不需要跟踪哪个用户与之关联。至少对于最初的应用程序！

1 个解决方案

#1

Many-to-many:

许多一对多：

CREATE TABLE ListsUsers (
    list_id ...,
    user_id ..., 
    PRIMARY KEY(list_id, user_id),
    INDEX(user_id, list_id)
) ENGINE=InnoDB;

Then use suitable JOINs to connect the tables when doing a SELECT.

然后在执行SELECT时使用合适的JOIN来连接表。

#1