2

It's not exactly many-to-many relationship. For example: I have a User table and a Role table. The constraint is a user can have 0-5 roles and a role can be assigned to many users.

How to model this in database? Thanks

EDIT: I'm looking for some standard solution on database side for this model. There are similar scenarios like above. For example: user password history: One user will have max 10 previous passwords stored in pwd_history table. It's kind of one-to-(0-10) relationship.

But seems to me there is no standard solution on database side. (@Branko's solution (2) below looks good though. ) I guess the best practice for this model is to enforce on client side, making these numbers configurable in property file and implementing client logic to handle this.

Eric
  • 1,031
  • 4
  • 14
  • 29
  • 1
    Out of curiosity, what's the purpose of limiting the roles to 5 maximum? If this is just the total number of roles than it's the same as a many-to-many, as opposed to a hard limit on some number. – jefflunt May 08 '13 at 19:39
  • Ha, I think you mean "many-to-many"? – Chris Dutrow May 09 '13 at 01:56

5 Answers5

4

There are 3 strategies:

  1. Just model it as a normal many-to-many in the database, but enforce the limit in triggers or (less ideally) client code.

  2. Model it as many-to-many, but place additional constraints to limit the number of rows:

    enter image description here

    CHECK (ROLE_NO IN (1, 2, 3, 4, 5))

    The combination of the UNIQUE constraint U1 on {USER_ID, ROLE_NO} and the above CHECK ensures there cannot be more than five USER_ROLE rows belonging to same user.

  3. Simply have five NULL-able 1-to-many relationships:

    enter image description here

Out of all these strategies, my first instinct would be to go for (2) - it's probably the "cleanest" and lends itself to easy modification, should your limits ever change.

The (3) might be more performant in some scenarios (although it might also be slower in others) but is awkward to work with and definitely less flexible.

The (1) will be a pain to implement properly - you'll have to do your locking very carefully to avoid concurrency issues and will probably destroy the scalability in the process.

Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
  • I'm not sure I understand your second example. I assume you already put a number from 1 to 5 in ROLE_NO, so when is it ever going to be six? Your constraint is already fulfilled, until it isn't, and now you've got too many records in the group. How do you get the next ROLE_NO in a group? Do you have to query the database first? – Robert Harvey May 08 '13 at 20:14
  • @RobertHarvey I'm sorry, I don't understand what you mean by "too many records in the group". The DBMS will enforce both constraints at all times, so you can never have more than 5 rows per user. As for the querying - it depends. If the client already knows which "slots" it took, then it may not be necessary to query the database (depending on concurrent clients). Otherwise, constructing such a query is easy enough, especially if your DBMS supports analytical functions. Even with the query, that doesn't necessarily mean additional database round-trip, if it's placed in a trigger. – Branko Dimitrijevic May 08 '13 at 20:25
  • I can buy the argument that you can create an analytical function in the database to do this, but not the client holding the necessary knowledge to perform an insert, or having to retrieve all the existing roles beforehand. Really, the database should be entirely responsible; the client should be able to hand the database a new User+Role, and it will either succeed or fail on its merits. – Robert Harvey May 08 '13 at 20:39
  • In any case, subject to that caveat, +1 from me. – Robert Harvey May 08 '13 at 20:40
2

Create a UserRoles Table

UserRoleID    PK
UserID        FK
RoleID        FK

You'll have to enforce the 5 role constraint with an INSERT trigger (example here), or in your application's business logic.

Community
  • 1
  • 1
Robert Harvey
  • 178,213
  • 47
  • 333
  • 501
1

The implementation is the same as many-to-many, but you will have to limit the number of roles in your relational table in your coding, avoiding to add more data when the limit of 5 is reach.

Math
  • 3,334
  • 4
  • 36
  • 51
0

Perhaps this is not a normalized design, but for small number of roles, you can just add 5 nullable columns to user, role1, role2 etc, although this would complicate update.

Alexander
  • 4,153
  • 1
  • 24
  • 37
  • It will complicate a lot of things. For example, you will need five separate queries or checks to determine if someone is assigned a role. – Robert Harvey May 08 '13 at 19:37
  • 1
    Wouldn't this query work? select * from users where userId = 1 and (role1 = 1 or role2 = 1 ...) etc. Also, you can always extract entire record to server side and check there. – Alexander May 08 '13 at 19:41
  • Sure. But what if the user is assigned less than five roles, and you have to add one? You will need logic to find the empty slot. – Robert Harvey May 08 '13 at 19:43
  • Yes, this is why I added note about complicated update :) – Alexander May 08 '13 at 19:43
  • 1
    It's a non-starter. If the company decides to change the requirements to six roles you are royally screwed. In a normalized database, it's a simple matter of changing the constraint. – Robert Harvey May 08 '13 at 19:44
0

I have not used it in DB, but when using in c language for other purpose, I have followed this method.

Consider Role1, Role2 ... Role5

Role  => Bit-Pattern => Number
-------------------------------

Role1 => 00000001 => 2
Role2 => 00000010 => 4
Role3 => 00000100 => 8
Role4 => 00001000 => 16
Role5 => 00010000 => 32

Maintain single column called roles in DB. And follow this,

Role1 + Role2  ==> 2 + 4 ==> 6
Role1 + Role2 + Role5  ==> 2 + 4 + 32 ==> 38
Role3 + Role4 ==> 8 + 16 ==> 24

Table would contain only 38 for user of Role1 + Role2 + Role5.

So to check if user has role role3, do as Role3 ==> role_col OR Role3 ie, (role_column || 8) would return true.

It saves new table; can hold different numbers upto as many bits in the field (8 here. 8 bit = 1 byte); Single simple query without even a join.

VoidPointer
  • 3,037
  • 21
  • 25
  • What if there are more than 32 or 64 roles? – Alexander May 08 '13 at 19:50
  • You need 32 or 64 bit width only. so 8-byte max here.. – VoidPointer May 08 '13 at 19:52
  • 2
    Sorry but this is probably a bad idea. You are violating the principle of [atomicity](http://en.wikipedia.org/wiki/1NF#Atomicity) and therefore the 1NF. As a consequence, the DBMS cannot maintain referential integrity for you, and you'll have trouble indexing, and therefore efficiently querying this "bitmap". Others have already pointed out that the total number of roles would be limited that way. If you can live with all that (e.g. because roles are very few and completely static), then this could actually be a very compact and quick solution, but it is not a general solution. – Branko Dimitrijevic May 08 '13 at 20:11
  • @BrankoDimitrijevic, I accept the indexing point. I have used this method to store different services a user could subscribe where I dint need to index this bitmap field. Would this bitmap method suit best when indexing is not necessary, in sense, no bitmap field in WHERE clause at all ? Your help is much appreciated.. – VoidPointer May 08 '13 at 20:19
  • @VoidPointer Depends on how you want to query - if you never search for users that belong to the given role, then you don't need that index. There is also the aspect of referential integrity. You yourself will have to ensure only the bits corresponding to the existing roles can be set. For example, if there are 7 roles, you'll have to "protect" the highest bit of the 8-bit field from being set. This is probably fine if roles are static so you can "bake" that logic into the code, but could be surprisingly hard to do in a concurrent environment if roles are dynamic. – Branko Dimitrijevic May 08 '13 at 20:32
  • Bitmapping as you've described it always is trying to store more than one piece of information in one column, which violates [First Normal Form](http://en.wikipedia.org/wiki/First_normal_form) if in a relational DB. You may have designed the DB and software so that you do not NEED to query on the subparts of the bitmap, but this also locks the data into a place where one CAN NOT naturally join on the subparts of the bitmap. This limits the flexibility of the design going forward. Also the data becomes unintelligible if the bit-pattern mapping ever changes anywhere in the codebase. – pcurry May 08 '13 at 20:37
  • Yeah I agree your points, @BrankoDimitrijevic and pcurry. Thanks for the information.. – VoidPointer May 08 '13 at 20:46