Suppose you have an application, there is such a case, the application of "users" and "group", the two concepts, the "group" is small groups of users, like interest groups that appearance, requirements to:
1, we give the user name, query he belong to which a group;
2, we give a group name, what are the user query group,
I think so at present is:
1, must have a "user" table, but do you want to add an attribute to the "user" table, record what every man took part in the group?
2, also seems to have a "group" table, in addition to the group name and other attributes, is sure to record each "group" contains which users,
Then I have some doubts:
1, if the "user" table, each user has a list of the group; The "group" in the table, each group has a list of users; So I feel some redundancy, and the future is bound to maintain the consistency problem, for example, to add a user in a group, you must ensure to the user to add a group, there is something wrong in one thousand the middle, or increasing the software in the future, is formed in maintaining what mistake?
2, single see "group" list: if only I create a table to record all the "group", so each "group" should only use a property (a field) to record it contains which customer?? If so, then I will have to give this field defines a format, such as "user1. User2. User3; . ", that is my own take on the part of data management work, don't feel right?
3, assume that "user" table does not contain the user's information "group", in order to eliminate the consistency, so when it comes to query the user name he belongs to the group, not have to traverse the entire table "group"? When a large amount of data the feeling is very slow,
4, similar to "3", if the "set" table does not record user information, so as to query a group contains which users, also have the same trouble,
How to design this kind of circumstance, please? I wish I clearly stated my problem, thank you,
CodePudding user response:
Generally,Group table: id, name, group attribute
The users table: user id, group id of the user name, user attributes, belong to
- group table not to record user information, when you need to query the set of users can use the group id associated to query the user table
Great - users, can in the user table index, improve the query speed
CodePudding user response:
Table design need to see the relationship between the two tables, you this example, there should be two things,First if you need is a user can only join a group, then the relationship between the two tables is more of a relationship, you can in the user table to join the group id, concrete such as the design on the second floor,
The second case, if your need is a user can join multiple groups at the same time, should design three tables,
The users table: user id, user name, user attributes,
Group table: id, name, group attribute
And set of tables: id, user id, group id