在 SQL 表中存储项目数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14141028/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Store array of items in SQL table
提问by user1375026
I know this has probably been asked a million times but I can't find anything definite for me. I'm making a website involving users who can build a list of items. I'm wondering what would be the best way for store their items in an SQL table?
我知道这可能已经被问了一百万次，但我找不到任何确定的东西。我正在制作一个涉及可以构建项目列表的用户的网站。我想知道将他们的项目存储在 SQL 表中的最佳方式是什么？
I'm thinking will I need to make a seperate table for each user since there I can't see any way to store an array. I think this would be inefficient however.
我在想我是否需要为每个用户制作一个单独的表，因为我看不到任何存储数组的方法。然而，我认为这将是低效的。
回答by a_horse_with_no_name
Depending on what an "item" is, there seem to be two possible solutions:
根据“项目”是什么，似乎有两种可能的解决方案：
- a one-to-many relationship between users and items
- a many-to-many relationship between users and items
- 用户和物品之间的一对多关系
- 用户和物品之间的多对多关系
If a single item (such as a "book") can be "assigned" to more than one user, it's 2). If each item is unique and can only belong to a single user it's 1).
如果单个项目（例如“书”）可以“分配”给多个用户，则为 2）。如果每个项目都是唯一的并且只能属于一个用户，则为 1)。
one-to-many relationship
一对多关系
create table users
(
user_id integer primary key not null,
username varchar(100) not null
);
create table items
(
item_id integer primary key not null,
user_id integer not null references users(user_id),
item_name varchar(100) not null
);
many-to-many relationship:
多对多关系：
create table users
(
user_id integer primary key not null,
username varchar(100) not null
);
create table items
(
item_id integer primary key not null,
item_name varchar(100) not null
);
create table user_items
(
user_id integer not null references users(user_id),
item_id integer not null references items(item_id)
);
Because of your extremely vague description, this is the best I can think of.
因为你的描述非常模糊，这是我能想到的最好的。
There is no need to use an array or something similar. It seems you are new to database modelling, so you should read up about normalisation. Each time you think about "arrays" you are probably thinking about "tables" (or relations).
不需要使用数组或类似的东西。您似乎是数据库建模的新手，因此您应该阅读有关规范化的内容。每次想到“数组”时，您可能都会想到“表”（或关系）。
Edit (just saw you mentioned MySQL):
the above SQL will notcreate a foreign key constraint in MySQL (even though it will run without an error) due to MySQL's stupid "I'm not telling you if I can't do something" attitude. You need to define the foreign keys separately.
编辑（刚刚看到你提到了 MySQL）：由于 MySQL 的愚蠢“如果我不能做某事，我不会告诉你”，
因此上面的 SQL不会在 MySQL 中创建外键约束（即使它运行时不会出错）态度。您需要单独定义外键。
回答by J Murrell
A separate table for each user\account would be best. This will limit the size of the necessary tables and allow for faster searching. When you present data you are usually displaying data for that current user/account. When you have to search through the table to find the relative information. The application will start to slow down the larger the dependent table grows. Write the application as if it will be used to the fullest extent of SQL. This will limit the need for redesign in the future if the website becomes popular.
每个用户\帐户的单独表最好。这将限制必要表的大小并允许更快的搜索。当您呈现数据时，您通常会显示该当前用户/帐户的数据。当您必须通过表格进行搜索以查找相关信息时。随着依赖表的增长，应用程序将开始减慢速度。编写应用程序，就好像它将在 SQL 的最大范围内使用一样。如果网站变得流行，这将限制未来重新设计的需要。
