创建MySQL表的问题，为什么有些人创建两个彼此相关的表而不是一个？

I'm very confused in creating Tables in MySQL because I read some PHP tutorials and the authors create two tables in their database and then linking them up later in PHP using joins.

我在创建MySQL中的表时非常困惑，因为我阅读了一些PHP教程，作者在他们的数据库中创建了两个表，然后在PHP中使用连接将它们链接起来。

Here's an example I got from an article..

这是我从文章中得到的一个例子..

Here the author created the table users and groups.

这里作者创建了表用户和组。

    CREATE TABLE users (
       id int(8) NOT NULL auto_increment,
       name varchar(255) NOT NULL default '',
       pass varchar(255) NOT NULL default '',
       PRIMARY KEY (id)
    ) TYPE=InnoDB;

   CREATE TABLE groups (
       uid int(8) NOT NULL default '0',
       grp varchar(255) NOT NULL default ''
   ) TYPE=InnoDB;

When I look at it, isn't the groups table not necessary? I mean I can just create the table this way:

当我看着它，是不是没有必要的群组表？我的意思是我可以这样创建表：

CREATE TABLE users (
       id int(8) NOT NULL auto_increment,
       name varchar(255) NOT NULL default '',
       pass varchar(255) NOT NULL default '',
       grp varchar(255) NOT NULL default ''
       PRIMARY KEY (id)
    ) TYPE=InnoDB;

In this way, I can just query the database and I don't need to join the two tables together at all.

通过这种方式，我可以只查询数据库，我根本不需要将两个表连接在一起。

So what's the purpose of creating two tables and not one? Please explain to me why linking together two tables is better than one.

那么创建两个表而不是一个表的目的是什么？请向我解释为什么将两个表连接在一起比一个表更好。

4 个解决方案

#1

I a lot of authentication systems 1 user can have multiple groups. Therefore 2 tables are necessary. If you want to have a good idea how to design your tables than you can have a read on database normalization.

我很多认证系统1用户可以有多个组。因此需要2个表。如果你想知道如何设计表，那么你可以阅读数据库规范化。

even beter would be if you create a table for groups, a table for users, and a table for users in groups. This is because it's common to have multiple users in a group and users can have multiple groups (many to many relation). Many to many relations in relational databases are not possible. Therefor you need the extra table.
An other advantage of this system is that if you decide to change the group name than you don't need to change that name for each user. You can just change the name once and it's changed for all users.

如果你为组创建一个表，为用户创建一个表，为组中的用户创建一个表，那么就更好了。这是因为组中有多个用户并且用户可以拥有多个组（多对多关系）是很常见的。关系数据库中的多对多关系是不可能的。因此你需要额外的桌子。此系统的另一个优点是，如果您决定更改组名，则不需要为每个用户更改该名称。您只需更改一次名称，即可为所有用户更改名称。

You get something like this.

你得到这样的东西。

- table user
  - id
  - username
  - password
  - etc

- table groups
  - id
  - name
  - etc

- table usergroups
  - userid
  - groupid

You can also have a look at FCO-IM which is a more complicated method to design databases compared to ERM, but might be handy for bigger and more complicated databases.

您还可以查看FCO-IM，与ERM相比，这是设计数据库的一种更复杂的方法，但对于更大，更复杂的数据库可能更方便。

#2

the keyword is 'normalization'

关键字是'规范化'

you have only one table. and because your lazy or tired you decide after a week to type 'mod' instead of 'moderator'... not good

你只有一张桌子。并且因为你懒惰或疲惫，你决定在一周后输入'mod'而不是'主持人'......不好

if you use two (or three) tables you only have to select / type in an ID, and you can easily apply one users to multiple groups

如果你使用两个（或三个）表，你只需要选择/输入一个ID，你就可以轻松地将一个用户应用于多个组

CREATE TABLE users (
       user_id int(8) NOT NULL auto_increment,
       name varchar(255) NOT NULL default '',
       pass varchar(255) NOT NULL default '',
       PRIMARY KEY (user_id)
    ) TYPE=InnoDB;

   CREATE TABLE groups (
       group_id int(8) NOT NULL auto_increment,
       name varchar(255) NOT NULL default '',
       PRIMARY KEY (group_id)
   ) TYPE=InnoDB;

CREATE  TABLE user_groups (
  user_id INT(8) NOT NULL ,
  group_id INT(8) NOT NULL ,
  PRIMARY KEY (user_id, group_id) ,
  INDEX user_id (user_id ASC) ,
  INDEX group_id (group_id ASC) ,
  CONSTRAINT user_id FOREIGN KEY (user_id ) REFERENCES users (user_id ) ON DELETE CASCADE,
  CONSTRAINT group_id FOREIGN KEY (group_id ) REFERENCES groups (group_id ) ON DELETE CASCADE
)
ENGINE = InnoDB;

#3

In your example (where there's only one table) you can only have a one to one relationship between a user and a group. (i.e.: A given user can only belong to one group at most.)

在您的示例中（只有一个表），您只能在用户和组之间建立一对一的关系。（即：给定用户最多只能属于一个组。）

However, if you separate the users and groups you can easily have a much more extensive set of relationships (one to one, one to many, etc.) such as allowing a single user to belong to multiple groups, etc. whilst still being able to trivially lookup group membership for a given group. That said, you will need a third "user to group membership" table which would contain an entry for each group a given user is a member of.

但是，如果您将用户和组分开，则可以轻松地拥有更广泛的关系集（一对一，一对多等），例如允许单个用户属于多个组等，同时仍然能够轻而易举地查找给定组的组成员资格。也就是说，您将需要第三个“用户到组成员身份”表，其中包含给定用户所属的每个组的条目。

#4

That's a weird way to do it. Usually, you want a users table that has the users information (avatar, information, etc) and a groupId that is a reference to a group that exists within a groups table. Using this concept allows you to have a groups table where each group has its own information such as avatar, description, and/or groupowner. It basically reduces duplication and allows you to extend relationships.

这是一种奇怪的方式。通常，您希望用户表具有用户信息（头像，信息等）和groupId，它是对组表中存在的组的引用。使用此概念，您可以拥有一个组表，其中每个组都有自己的信息，例如头像，描述和/或组合所有者。它基本上减少了重复，并允许您扩展关系。

#1