1

Part I

I have a database model with members and organizations with each organization has many members, so it is clear that is a one-to-many relationship. The difficulty is that among those members there should be exactly one admin and the others are just members. How to model this situation?

I thought about adding an admin_id as a foreign key in the organization table that reference a member in the member table instead of adding a role attribute to the member table. Does this approach fulfill the requirement?

Part II

the application require that alongside an admin, there should be a super admin who can view, edit and delete etc... all the organizations, but the super admin is not a member within an organization. So how can i model the super admin in this situation? does an orphan table that represent super admins fulfill the requirement? if not, what is the best way to model my database based on those requirement?

i tried Database model with users, roles and rights but look like it does not meet my use case. can a solution be derived from the previous link?

Edit

for the sake of clarification, here is every role with its privileges super admin: create, delete and edit an organization, assign admin to each organization, add members to each organization

admin: manage member within an organization

AmineBinOthman
  • 11
  • 2

1 Answers1

0

The user role (member/admin/super) is one solid property, and this is a member property. So I recommend you to create one column in member table which stores the member role flag:

CREATE TABLE member (
    member_id ..,
    org_id .. REFERENCES organization (id),
    member_role SET('org_admin', 'super_admin'),
    UNIQUE (org_id, (CASE WHEN FIND_IN_SET(member_role, 'org_id') THEN 1 END)),
    UNIQUE ((CASE WHEN FIND_IN_SET(member_role, 'super_id') THEN 1 END)),
    ...

Created unique indices does not allow to have more than one admin per organizaton and more than one superadmin per the whole system. It is possible that these are excess restrictions. If so then remove according index(es).

It is possible that there exists a rule that the organization cannot not have an admin (or the whole system cannot not have a superadmin). But these are legal system states (for example, when you create new organization which have no members yet). You must check such restrictions on the application level and produce according warnings.

Akina
  • 20,750
  • 2
  • 20
  • 22