In my application i need to assign multiple groups to my users. There are 1000+ users and 10-15 groups.
在我的应用程序中，我需要为我的用户分配多个组。有1000多个用户和10-15个组。
Which database design is better?
哪个数据库设计更好?
One-to-many:
一对多的:
USER_ID | GROUP_1 | GROUP_2 | ... | GROUP_15
--------------------------------------------
1 | true | false | ... | true
2 | false | true | ... | true
3 | true | true | ... | true
. | . | . | ... | .
. | . | . | ... | .
. | . | . | ... | .
or many-to-many:
或者多对多的:
USER_ID | GROUP_ID
------------------
1 | 1
1 | 15
2 | 2
2 | 15
3 | 1
3 | 2
3 | 15
. | .
. | .
. | .
?
吗?
3 个解决方案
#1
2
The many-to-many is the better design without a doubt.
多对多无疑是更好的设计。
The first design makes writing queries difficult. Consider the following routine queries.
第一个设计使编写查询变得困难。考虑以下常规查询。
- Is a specified user in a specified group? To do this you have to use a different query for each group. This is undesirable. Also if you are using column names for groups, then the list of groups is part of the database schema rather than being part of the data, where the users are data.
- 是否指定组中的指定用户?为此，必须对每个组使用不同的查询。这是不可取的。另外，如果您使用的是组的列名，那么组列表是数据库模式的一部分，而不是数据的一部分，用户是数据。
- What groups is a specified user in? You could simply return the single row, though many applications would probably prefer (and are versed in) iterating through a result set. Iterating through a subset of columns is doable but unnatural.
- 哪些组是指定的用户?您可以简单地返回单行，尽管许多应用程序可能更喜欢(并且很精通)遍历结果集。
- What users does a specified group contain? Now you are back to the different queries for each group.. I'll leave the demonstration of these things as an exercise to the reader. The relational model, which SQL databases approximate, was intended to deal with relations and keys (tables and primary/foreign keys). Information should exist in one (and ONLY ONE) place AS DATA (not metadata). The multi-column approach lacks normalization and will be a maintenance headache into the future.
- 指定的组包含哪些用户?现在您回到了针对每个组的不同查询。我把这些演示留给读者作为练习。SQL数据库近似的关系模型旨在处理关系和键(表和主/外键)。信息应该作为数据(而不是元数据)存在于一个(且仅存在于一个)位置。多列方法缺乏规范化，在未来将是一个棘手的维护问题。
Note: I edited this response to correct a misreading on my part of the original code. The thrust of the comments is the same however. The second (many-to-many) is the way to go.
注意:我编辑了这个响应，以纠正对原始代码部分的误读。然而，这些评论的主旨是一样的。第二种方法是多对多。
#2
0
No 2 is standard, you can increase number of groups at any time, also you can handle easy sql join queries easily.
2是标准的，您可以随时增加组的数量，也可以轻松地处理简单的sql连接查询。
#3
0
If you want to follow the rules of an entity relationship model:
如果您想遵循实体关系模型的规则:
Many-to-many: users can belong to different groups & groups can have multiple users.
One-to-many: a user belongs to one group & groups can have multiple users.
Your second example is a many-to-many, your first isn't a one-to-many. A one-to-many would be:
第二个例子是多对多，第一个不是一对多。一对多的是:
USER_ID | GROUP_ID
------------------
1 | 1
2 | 15
3 | 2
4 | 15
5 | 1
6 | 2
7 | 15
Where user_id must be unique.
user_id必须是唯一的。
#1
2
The many-to-many is the better design without a doubt.
多对多无疑是更好的设计。
The first design makes writing queries difficult. Consider the following routine queries.
第一个设计使编写查询变得困难。考虑以下常规查询。
- Is a specified user in a specified group? To do this you have to use a different query for each group. This is undesirable. Also if you are using column names for groups, then the list of groups is part of the database schema rather than being part of the data, where the users are data.
- 是否指定组中的指定用户?为此，必须对每个组使用不同的查询。这是不可取的。另外，如果您使用的是组的列名，那么组列表是数据库模式的一部分，而不是数据的一部分，用户是数据。
- What groups is a specified user in? You could simply return the single row, though many applications would probably prefer (and are versed in) iterating through a result set. Iterating through a subset of columns is doable but unnatural.
- 哪些组是指定的用户?您可以简单地返回单行，尽管许多应用程序可能更喜欢(并且很精通)遍历结果集。
- What users does a specified group contain? Now you are back to the different queries for each group.. I'll leave the demonstration of these things as an exercise to the reader. The relational model, which SQL databases approximate, was intended to deal with relations and keys (tables and primary/foreign keys). Information should exist in one (and ONLY ONE) place AS DATA (not metadata). The multi-column approach lacks normalization and will be a maintenance headache into the future.
- 指定的组包含哪些用户?现在您回到了针对每个组的不同查询。我把这些演示留给读者作为练习。SQL数据库近似的关系模型旨在处理关系和键(表和主/外键)。信息应该作为数据(而不是元数据)存在于一个(且仅存在于一个)位置。多列方法缺乏规范化，在未来将是一个棘手的维护问题。
Note: I edited this response to correct a misreading on my part of the original code. The thrust of the comments is the same however. The second (many-to-many) is the way to go.
注意:我编辑了这个响应，以纠正对原始代码部分的误读。然而，这些评论的主旨是一样的。第二种方法是多对多。
#2
0
No 2 is standard, you can increase number of groups at any time, also you can handle easy sql join queries easily.
2是标准的，您可以随时增加组的数量，也可以轻松地处理简单的sql连接查询。
#3
0
If you want to follow the rules of an entity relationship model:
如果您想遵循实体关系模型的规则:
Many-to-many: users can belong to different groups & groups can have multiple users.
One-to-many: a user belongs to one group & groups can have multiple users.
Your second example is a many-to-many, your first isn't a one-to-many. A one-to-many would be:
第二个例子是多对多，第一个不是一对多。一对多的是:
USER_ID | GROUP_ID
------------------
1 | 1
2 | 15
3 | 2
4 | 15
5 | 1
6 | 2
7 | 15
Where user_id must be unique.
user_id必须是唯一的。