How to model a user/group relationship where users are assigned an unique number within the group? 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 ?
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)