How to draw ER diagram for recursive relation when the number of roles is more than 3

database-designoraclerecursive

I know for a recursive relation where there are 2 roles, for example:
Worker works for Manager
and Worker and Manager both are 2 roles of Employee, we show it this way:

But what if Employee had roles like:
1. Director
2. Manager
3. Secretary
4. Worker
and every role in the lower hierarchy works for the ones in the higher rank.

For Example:
Manager works for Director, Secretary works for Director and, Worker Works for Director

Then again,
Secretary and Worker also Works for Manager and so on…

How to show that in the ER DIAGRAM?

Best Answer

Organisations are generally far more flexible than we initially think. For example, Jon might have two jobs in the organisation (Developer and Acting Team Lead).

He may report to Bill the Development Manager for his Acting Team lead role and to Bob for the Development work that he still carries out.

This necessitates having the self referencing relationship for REPORTS_TO expand out into another entity type so that each person can report to many others rather than just one. You also need to be able to show that each person can have many roles/jobs and the you may want to have some roles that officially report to multiple roles.

From another perspective hierarchies like this are worth exploring in a graph database as that is what they excel at. Not having a formal schema also means that they are very flexible.

Related Solutions

Oracle: how to set only some roles as not default for a user (no GUI)

You have to use the GRANT command to grant roles (with admin option if required), then use ALTER USER to set roles to default (or not). So, the order of commands is,

grant roles (with admin option as needed)
alter user to set all roles to non-default
alter user to set only required roles to default.

A way to generate sql using sql is as follows, you can run this sql in instanceA, and actually execute the generated script in instanceB by replacing username if required. Make sure to test though,

with x as (select 'USER1' as usr from dual)
select cmd from (
select 1 as ord, 'grant ' || granted_role || ' to ' || grantee || case when admin_option = 'YES' then ' with admin option' end  || ';' as cmd
from dba_role_privs, x where grantee = x.usr 
union all
select distinct 2 as ord, 'alter user ' || grantee || ' default role none;' as cmd from dba_role_privs, x where grantee  = x.usr
union all
select 3 as ord, 'alter user ' || grantee || ' default role ' || granted_role || ';' as cmd from dba_role_privs, x where grantee  = x.usr and default_role = 'YES')
order by ord

Database Design – Using Many-to-Many Relation with Weak Entity

I'd say that this is pretty standard stuff ;

Create table employees (id int primary key, 
                        name varchar(100)
                        -- Plus some additional columns
                        ) 

Create table responsibilities (id int primary key, 
                               description varchar(100)
                               --Plus some additional columns;
                               ) 

Create table employee_responsibilities ( employee_id int foreign key references employees(id), 
                                         responsibility_id int foreign key references responsibilities(id) 
                                         --Plus some additional columns;
                                       )

You might want to have a surrogate primary key on the relationship table ;

Create table employee_responsibilities (id int primary key, 
                                        employee_id int foreign key references employees(id), 
                                        responsibility_id int foreign key references responsibilities(id) 
                                        --Plus some additional columns;
                                        )

Best Answer

Related Solutions

Oracle: how to set only some roles as not default for a user (no GUI)

Database Design – Using Many-to-Many Relation with Weak Entity

Related Question