I wish to make user group system that mimics group policy in instant messengers.

Each user can make as much as groups because they want, however they cannot have groups with duplicate names, plus they can put as numerous buddies because they want into any groups.

For instance, John's friend Jen could be in 'school' number of John and 'coworker' number of John simultaneously. And, it's totally independent from how Jen puts John into her group.

I am thinking two possiblity to implement this in 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)
)

Within this situation, all groups possessed by all customers may have a distinctive id. So, id alone can identify which user and also the title from the group.

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)
)

Within this situation, group_id always begins from for every user, so, there may exist many groups with same group_id s. But, pk pair (user_id, group_id) is exclusive within the table.

which strategy is better implementation and why? what exactly are advantages and disadvantages for every situation?

EDIT: added AUTO_INCREMENT to group_id in second scenario to insure it's auto-designated from for every user_id.

EDIT: 'better' means... - better performance in Choose/Place/UPDATE buddies towards the group since that'll be the mostly used procedures concerning the user group. - robustness of database like which could be more safe when it comes to user size. - recognition or general preference of each one over another. - versatility - extensibility - usability - simpler to make use of.

Personally, I'd opt for the first approach, however it is dependent how the application will work. Whether it would be feasible for possession of the group to become transformed, in order to merge user profiles, this is much simpler to complete inside your first approach compared to the second. Within the second approach, if either of individuals situations ever happen, you wouldn't just improve your user_group table, but any dependent tables too which have an overseas key regards to user_group. This may also be a many to a lot of relation (you will see multiple customers inside a group, along with a user is a person in multiple groups), so it may need another joining table. Within the first approach, this really is fairly straightforward:

group_member (
  group_id int,
  user_id int
)

For the second approach, it might need a 3rd column, that won't simply be more confusing since you are now including user_id two times, but additionally require 33% additional space for storage (this might be an problem for the way large you anticipate your database to become):

group_member (
  owner_id int,
  group_id int,
  user_id int
)

Also, should you ever intend to range from MySQL to a different database platform, this behavior of auto_increment might not be supported. I understand in MS SQL Server, an auto_increment area (identity in MSSQL) will be incremented, not provided unique based on indexes up for grabs, so to obtain the same functionality you would need to implement it yourself.

Please define "better".

From the stomach, I'd select the second one.

The searchable pieces are divided more, but that would not be what I'd pick if place/update performance is an issue.

I see no possible help to number two whatsoever, it's more complicated, more fragile (it wouldn't work on all in SQL Server) and gains nothing. Recall the groupId is without meaning except to recognize an archive distinctively, likely the consumer willonly begin to see the group title not the id. Therefore it does not matter when they all begin with or maybe you will find gaps just because a group was folded back or erased.