Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

Nader_look's avatar

Need help for this complexe relation when create the database structure

Hey everyone, I work on a big project that has different functionality we do refactor on big code logic but I was confused a little bit when I want to create this logic in terms of database and best practice relation to not make complex queries :

  • You create a company with one owner and multiple super admins but both have the same Permissions in the console
  • inside this company owner and super admins can create a department each department have users with different roles (exp: manager, supervisor, simple user have only read-only permission) in our case when creating department roles created automatically and when you assign a user inside the department you give him roles and they are attached in our intermediate table that has ('user_id', 'department_id', 'role_id'), the manager only sees and make actions of the department that owner give it to him, and owner and super admins have the action to all departments ....
  • company have many departments, department belongs to one company.
  • user belongs to one company,
  • user belongs to many departments with different roles

questions : I attach owner and super admins to the company and each time department is created I query the owner and super admins I do not attach owner and super admins to any department and I work with deep relations between company department users isolate the owner and admins with own table role have company_id department with his own role with the intermediate table have dep_id role_id user_id

0 likes
3 replies
Tray2's avatar
Tray2
Best Answer
Level 73

That doesn't sound too complex. As long as you just use one users table and one user_roles table you should be good.

I also suggest you read my post on designing the database. https://tray2.se/posts/database-design

Nader_look's avatar

@Tray2 I know this is good for users and roles but the department has its own role, I put the roles of the department ith the roles table also the admin and the owner or I separate this

Please or to participate in this conversation.