Designing User & its Sub User Database Diagram Problem

database-designdatabase-diagramserdoraclesqlplus

  1. There are some business category e.g. Travel Agency, Hotel, Car Rental etc.
  2. Roles:
  • Management rolesSuper Admin, Staff
  • Customer side roles are Owner, admin and sales counter
  • May be etc. customer side roles will be added
  1. Status i.e. On-Hold, Active, In-Active, Blocked, Deleted etc.
  2. Scenario:
  • Each user in the database has its role and status with other credentials
  • Super Admin or Staff creates a customer (USER) with category e.g. "Travel Agency" and provide owner details
  • Now this Travel Agency (USER) has its own SUB_USERS that may be one or many

Now I am confused between user and sub users.
The work that I done so far is as following in shape of ERD:
enter image description here

Please put me on the right path as to how can I create a USER with its SUB USERS?

Thanks in anticipation

Best Answer

Why not just get rid of the main_user and sub_user tables and have a nullable field on the users table called parent_user_id. Then you can use a self-join on users.parent_user_id = users.user_id to get all the sub-users for a given Travel Agency. (When the parent_user_id field is null, that's a Travel Agency.)

Related Question