4

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 ?

4
  • A user can be assigned with different numbers in different groups? Jan 15, 2013 at 18:08
  • If a user is removed from a group, can his number be reused, reassigned to another user in the future? Jan 15, 2013 at 18:10
  • If a user is removed from a group, and then joins it again, should the previous number be used again or a new number assigned to her? Jan 15, 2013 at 18:13
  • @ypercube: Yes, a user can be assigned with different numbers in different groups. His number can eventually be reassigned and he may regain the same number if it is available and he rejoins the same group (but the admin may choose a different one for him).
    – ereOn
    Jan 15, 2013 at 18:24

1 Answer 1

3

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)
2
  • Thanks a lot ! Any chance you know how to modelize such a relationship with SQLAlchemy ? (But I guess this is rather a question for StackOverflow)
    – ereOn
    Jan 15, 2013 at 19:20
  • In case someone needed that in the future, here the related question on stack-overflow.
    – ereOn
    Jan 16, 2013 at 9:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.