SQL：具有多个子值的外键 [英] SQL: Foreign Key With multiple child values

问题描述

我如何设置与用户列表具有以下关系的组表：

How would I setup a groups table with following relationship with a list of user:

例如，假设我有一个用户表：

For example, lets say I have table of users:

              user_list
-----------------------------------
first_name | Last Name  | user_id |
-----------------------------------
   john    | Appleseed  |  4      |
   Jasmin  | Applejuice |  6      |

现在我想创建一个组表。如何创建一个表，可以将用户包含在一个组或多个组中。例如，John包含在 Apple_group 中，但也包含在 Orage_group 中。 Jasmin仅包含在 Orange_group 中：

Now I want to create a groups table. How can I create a table where users can be included in a group, or multiple groups. For example, John is included in the Apple_group but also included in the Orage_group. Jasmin is only included in the Orange_group:

                        groups
----------------------------------------------------
   group_name    | included_users_ids  | group_id  |
----------------------------------------------------
   Apple_group   |       4             |   1       |
   Orange_group  |       4, 6          |   6       |

假设user_list表已经存在，如何设置组表？此外，我已经阅读（请参阅下面的链接），我不应该在单个列中包含多个值。

Assuming the user_list table already exists, how would I setup the groups table? Furthermore I've read (see link below) that I shouldn't include multiple values in a single column.

插入具有多个值的外键

所以，我的问题是：

1. 设置组表的适当方法是什么？

1. What would be the appropriate way to setup the groups table?

这样做的SQL用途是什么？

What would the SQL be for doing so?

当想要将用户设置为a时，如何用PHP处理呢？组？

How would this then be handled with PHP when wanting to set a user to a group?

推荐答案

您需要创建第三个表，称为

You need to create a third table, referred to as a "join table".

users (
  id,
  name
);

groups (
  id,
  first_name,
  last_name
);

groups_to_users (
  user_id,
  group_id
);

然后在groups_to_users表中为用户所属的每个组创建一个条目（或者，您可以可以用另一种方式说，一个组中每个用户的一个条目）。这是一个n：n关系（多对多）。如果您搜索数据库规范化（如我在上一个问题的回答中提到的那样），您将学到所有有关此内容的信息。

Then create an entry in the groups_to_users table for every group a user is a member of (or, you could say it another way, one entry for every user that's in a group). This is an n:n relationship (many-to-many). If you search for "database normalization", as I mentioned to you in an answer to a previous question, you'll learn all about this stuff.

如果您想获取所有用户组，您将执行以下操作：

If you want to get all of a user's groups, you'd do something like:

SELECT g.* FROM groups_to_users gtu
LEFT JOIN groups g ON gtu.group_id=g.id
WHERE gtu.user_id = :UserId

如果您想获得网上论坛的用户，则可以执行以下操作：

If you wanted to get a group's users, you'd do:

SELECT u.* FROM groups_to_users gtu
LEFT JOIN users u ON gtu.user_id=u.id
WHERE gtu.group_id = :GroupId

这篇关于SQL：具有多个子值的外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！