2

In my application i need to assign multiple groups to my users. There are 1000+ users and 10-15 groups.

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

?

White Noise
  • 373
  • 2
  • 13
  • 2
    The first one is not "one to many" in any conventional sense, the second one is the design you should use. – Uueerdo Jan 11 '16 at 20:47
  • [This post](http://stackoverflow.com/questions/23194292/normalization-what-does-repeating-groups-mean) may be useful to you. – Bob Kaufman Jan 11 '16 at 20:48
  • Try to edit this question and rephrase it in a way that encourages specific answers instead of general opinions. In general the design of your database should be business specific. Often there will be multiple solutions that are equally acceptable. – SunSparc Jan 11 '16 at 20:59

3 Answers3

3

The many-to-many is the better design without a doubt.

The first design makes writing queries difficult. Consider the following routine queries.

  1. 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.
  2. 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.
  3. 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.

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.

Ken Clement
  • 748
  • 4
  • 13
1

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.

Kurt Van den Branden
  • 11,995
  • 10
  • 76
  • 85
0

No 2 is standard, you can increase number of groups at any time, also you can handle easy sql join queries easily.

Gouda Elalfy
  • 6,888
  • 1
  • 26
  • 38