mysql 中关系数据库表的最佳实践是什么？
我知道，那里有很多关于 mysql 的信息。但我真的无法找到这个具体且实际上简单的问题的答案：
假设我有两个表：
USERS （有很多字段，例如姓名、街道、电子邮件等）和 团体 （也有很多字段）
关系是（我猜？）1:n，即一个用户可以是多个组的成员。
我不喜欢的是创建另一个表，名为 USERS_GROUPS_REL。该表只有两个字段：
us_id（表USERS的唯一键）和 gr_id（表 GROUPS 的唯一键）
在 PHP 中，我使用连接进行查询。
这是“最佳实践”还是有更好的方法？
感谢您的任何提示！
大家好，
感谢您的快速和有用的支持。知道自己走在正确的道路上，我的 mysql 自信心增强了一些。 :-)
正如许多人评论的那样，我的示例不是 1:n，而是多对多。就像快速 sql 课程一样：:-)
这些术语正确吗？ 1:n 一对多 n:1 多对一 n:n 多对多？
I know, there is a lot of info on mysql out there. But I was not really able to find an answer to this specific and actually simple question:
Let's say I have two tables:
USERS
(with many fields, e.g. name, street, email, etc.) and
GROUPS
(also with many fields)
The relation is (I guess?) 1:n, that is ONE user can be a member of MANY groups.
What I dis, is create another table, named USERS_GROUPS_REL. This table has only two fields:
us_id (unique key of table USERS) and
gr_id (unique key of table GROUPS)
In PHP I do a query with join.
Is this "best practice" or is there a better way?
Thankful for any hint!
Hi all,
thanks for your quick and helpful support. Knowing that I was on the right way builds up my mysql-self-confidence a little. :-)
As many commented, my example is not 1:n but many to many. Just as a quick sql-lesson: :-)
Are these the right terms?
1:n one to many
n:1 many to one
n:n many to many?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您描述了多对多关系。利用这种关系，许多用户可以成为许多组的成员。就您的目的而言，这确实是正确的方法。
一对多和一对一关系不需要链接或交叉引用表 (USERS_GROUPS_REL)。
您可能会发现本教程很有用：
http://www.tonymarston.net /php-mysql/many-to-many.html
You have described a many-to-many relationship. Using that relationship, many USERs can be members of many GROUPS. For your purposes, this is indeed the correct way to go.
One-to-many and one-to-one relationships do not require the link or cross-reference table (USERS_GROUPS_REL).
You might find this tutorial useful:
http://www.tonymarston.net/php-mysql/many-to-many.html
这是最好的情况:)
在关系表中创建复合谓词主键是最佳解决方案：
It's quite the best case :)
Creating a compound predicate primary key in the relation table is the optimal solution:
这将是最好的做法。
连接两侧都有两个表，中间有一个映射表（或链接器表，或任何其他术语），用于处理多对多关系（一个组有许多用户，一个用户可以属于许多团体）。
为了提高性能，您应该使用
us_id和gr_id为映射表创建一个复合键。您还可以创建反向索引，这样如果您需要按gr_id排序，您也可以获得性能优势。That would be the best practice.
You have two tables on either side of the join and a mapping table (or linker table, or whatever other terminology is out there) in the middle which handles the Many-to-Many relationship (A Group has many Users and a User can belong to many Groups).
To increase performance, you should make a composite key for the mapping table out of
us_idandgr_id. You can also create an index on the inverse so that if you need to sort bygr_id, you get the performance benefit there too.一个用户可以属于多个组，一个组又包含多个用户，因此是多对多的关系。
你所描述的方式是典型且正确的。多对多关系通常与关联类或关系表进行映射。
有时关系表有更多列。例如，用户可以是特定组的管理员。 users_group_rel 表也会有一个字段
administrator。One user can belong to many groups, and one group contains many users, so it is a many-to-many relationship.
The way you describe it is a typical and correct one. A many-to-many relationship is often mapped with an association class or relation table.
Sometimes the relation table has more columns. For example, a user may be the administrator of a particular group. The users_group_rel table would then also have a field
administrator.