Geek Answers Handbook

Database design - how to implement a user group table?

I want to create a system of user groups that imitates group policy in instant messengers.

Each user can create as many groups as he wants, but they cannot have groups with duplicate names, and they can put as many friends as they want in any groups.

For example, John’s friend Jen can be in John’s “school” group and John’s “colleagues” at the same time. And it does not depend on how Jen puts John in her group.

I think of two possible ways to implement this in the database user_group table.

1.

user_group (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT,
group_name VARCHAR(30),
UNIQUE KEY (user_id, group_name)
)

In this case, all groups belonging to all users will have a unique identifier. Thus, only an identifier can identify a user and group name.

2.

user_group (
user_id INT,
group_id INT AUTO_INCREMENT,
group_name VARCHAR(30),
PRIMARY KEY (user_id, group_id),
UNIQUE KEY (user_id, group_name)
)

group_id 0 , group_id s. pk (user_id, group_id) .

? ?

EDIT: AUTO_INCREMENT group_id , , 0 user_id.

EDIT: "" ... - SELECT/INSERT/UPDATE , . - , . - . - - - - .

+5
3

1- , , . - , , , , . , - - , user_group, , user_group. - ( , ), . :

group_member (
  group_id int,
  user_id int
)

3- , , user_id , 33% ( , ):

group_member (
  owner_id int,
  group_id int,
  user_id int
)

, - MySQL , auto_increment . , MS SQL Server auto_increment (identity MSSQL) , , , , .

+3

, "".

.

, , , , , / .

0

I do not see a possible benefit for number 2 at all, it is more complex, more fragile (it does not work at all in SQL Server) and does not get anything. Remember that groupId does not make sense, except to uniquely identify the record, the user will probably see the name of the group, not the identifier. Therefore, it doesn’t matter if they all start with 0 or if there are spaces, because the group was dropped or deleted.

0
source

More articles:


All Articles