How to model a user/group relationship where users are assigned an unique number within the group

database-design

I am working on a web-server applications that deals with 2 entities:

  • users
  • groups

A user can belong to one, several or no group at all. Fairly usual, I assume.

However, I have additional constraints: each user should be able to receive a unique number (unique within the same group) in a "stable" and "deterministic" way, that is:

  • If a user is added, removed or updated, the existing users must keep their actual assigned numbers in every group.
  • The number should not be automatically assigned (not like an id. The number should be set explicitly by the web-server administrator when managing groups membership).

I haven't done a lot of Data Modelling and maybe this is dumb simple, by I can't decide which way to go and how to design my tables.

Do you guys have a suggestion ?

Best Answer

After your comments, I think you need a simple many-to-many table with an additional UNIQUE constraint:

TABLE UserGroups
  GroupID 
  UserID
  UserNumber
  PRIMARY KEY (GroupID, UserID)
  UNIQUE (GroupID, UserNumber)
  FOREIGN KEY (GroupID)
    REFERENCES Groups (GroupID)
  FOREIGN KEY (UserID)
    REFERENCES Users (UserID)