Database modeling

My question is about modeling a particular situation: In my model I have tables: User, Offices and Partners. The system has different user profiles: users of Office (counselors, principals, assistants), users for Partners (Institutions,..), admin users. What is the best way to model the relationship between office use开发者_如何学JAVArs and partner users if: - The users are unique (two users should have the same login - should be in only 1 table) - An office user is associated to only one office - A partner user is associated only with a partner.

It should be a table for office users and one for partners users? A many to many relationships in this case would not work, right? Thanks for your help.

You have not provided enough definition re Partners and Offices, so those parts of the model are not yet complete, but I think the main question you are asking is answered in this ▶Data Model◀. As I understand it:

• User (login, UserName is unique, and you do not want that to be unclear)
• Users are exclusively (that is the X in the half-circle) Office/Partner/Administrator
  • that requires a Supertype-Subtype structure. The cardinality is 1::0-1
• Users belong to (?) an Office or a Partner, exclusively.
  
  • That may be OfficeType or OfficeTitle.

If you define that last item a bit more, I can finish the model.

Readers who are unfamiliar with the Standard for Modelling Relational Databases may find the ▶IDEF1X Notation◀ helpful.

Assuming this model will never change I would add a field to the users to determine where they belong. You could put an enum named user_type with possible values of 'office', 'partner', or 'admin'.. And then you could put in columns of office_id, and partner_id so you could join the tables as needed.

I think the best route would be to go with an account group structure, where you use an association table to determine who's in what group...

group

id | name

group_assoc

group_id | account_id