2

I'm struggling in database design problem and it seems I can't get it out properly.

I need some help on this. Maybe it's a dumb case but I'd like to validate or correct my scheme and then go ahead.

The app should behave like this:

  • a user can be part of multiple accounts and have different roles (admin or not)
  • an account can have one or many groups
  • each group can have one or many members
  • a member can be part of many groups
  • a member can be either an user or a legal entity (company)

  • if it's a company, then the company is represented by a user

  • a user can represent 0,1 or many companies in different groups

Here is my actuel scheme where I have a n-to-m relationship on Groups-User-Companies. enter image description here

Here in GroupMembers, the users_id or companies _id will be filled either it's a legal entity or individual.

And the CompanyRepresentedBy table will contain the group_id, user_id and company_id

I'm not really sure of this scheme.

To have a better understanding, here is a diagram of the relationship Account-Group-Member-User Diagram

Thank you all!

PS: I can provide the MySQL Workbench file if needed

0

Why would you split into User and Account?

What is Group standing for? It's relating to Account and Company now, that I'm thinking to GroupType or separated tables AccountGroup and CompanyGroup

Above all, based on your current requirements, following my suggestions:

https://dbdiagram.io/d/5d6f2704ced98361d6de2d0d

a user can be part of multiple accounts and have different roles (admin or not)

Tables: User (Id), Account (Id) and UserAccount (UserId, AccountId)

an account can have one or many groups

Tables: Group (Id) and AccountGroup (AccountId, GroupId)

each group can have one or many members / a member can be part of many groups

Tables: Member (Id) and MemberGroup (MemberId, GroupId)

a member can be either an user or a legal entity (company)

Tables: Company (Id, MemberId), User (add MemberId)

if it's a company, then the company is represented by a user / a user can represent 0,1 or many companies in different groups

Tables: Company (add RepresentUserId), Group (add RepresentUserId)

Please noted that this is for an idea, not a fullfill solution. I hope this help you much.

  • Thanks for the reply Dan. A group is just grouping members. See it like departments. An account can have multiple groups and groups have members (which can be a company represented by a user OR a user). I can't use the relationship User (MemberId) and Company(id, MemberId) nor Company (RepresentUserId) because it means company will have just on representative only but in fact the company could be in 2 separate accounts and groups with different representatives. See my updated diagram in the first post for a better understanding – Bidibule Sep 4 at 12:17

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.