Databases
StackOverflow2013
Postsdbo
PODatabase design - how to implement user group table?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODatabase design - how to implement user group table?
    primarykey
    data
    text
    <p>I want to make user group system that imitates group policy in instant messengers.</p> <p>Each user can create as many as groups as they want, but they cannot have groups with duplicate names, and they can put as many friends as they want into any groups.</p> <p>For example, John's friend Jen can be in 'school' group of John and 'coworker' group of John at the same time. And, it is totally independent from how Jen puts John into her group.</p> <p>I'm thinking two possible ways to implement this in database user_group table.</p> <p>1.</p> <pre><code>user_group ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT, group_name VARCHAR(30), UNIQUE KEY (user_id, group_name) ) </code></pre> <p>In this case, all groups owned by all users will have a unique id. So, id alone can identify which user and the name of the group.</p> <p>2.</p> <pre><code>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) ) </code></pre> <p>In this case, group_id always starts from 0 for each user, so, there could exist many groups with same group_id s. But, pk pair (user_id, group_id) is unique in the table.</p> <p>which way is better implementation and why? what are advantages and drawbacks for each case?</p> <p>EDIT: added AUTO_INCREMENT to group_id in second scenario to insure it is auto-assigned from 0 for each user_id.</p> <p>EDIT: 'better' means... - better performance in SELECT/INSERT/UPDATE friends to the group since that will be the mostly used operations regarding the user group. - robustness of database like which one will be more safe in terms of user size. - popularity or general preference of either one over another. - flexibility - extensibility - usability - easier to use.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USJoon
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PODatabase design - how to implement user group table?
      1. USCodyrs
      1. VTFavorite
    2. VO
      singulars
      1. PODatabase design - how to implement user group table?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PODatabase design - how to implement user group table?
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload