Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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 ?

share|improve this question
A user can be assigned with different numbers in different groups? – ypercube Jan 15 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 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 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 at 18:24

1 Answer

up vote 2 down vote accepted

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)
share|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 at 19:20
In case someone needed that in the future, here the related question on stack-overflow. – ereOn Jan 16 at 9:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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