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 ?

  • A user can be assigned with different numbers in different groups? – ypercubeᵀᴹ Jan 15 '13 at 18:08
  • If a user is removed from a group, can his number be reused, reassigned to another user in the future? – ypercubeᵀᴹ Jan 15 '13 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? – ypercubeᵀᴹ Jan 15 '13 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 '13 at 18:24

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

TABLE UserGroups
  UNIQUE (GroupID, UserNumber)
    REFERENCES Groups (GroupID)
    REFERENCES Users (UserID)
| improve this answer | |
  • 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 '13 at 19:20
  • In case someone needed that in the future, here the related question on stack-overflow. – ereOn Jan 16 '13 at 9:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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