Entity Relationship Diagram – Entity depending on attribute

database-diagramsentity-frameworkrelational-theoryrelations

I have an ER-Diagram containing the entity employees. When employees have high skills they may supervise lower skilled employees. How can I model that in an ER-Diagram?

My initial approach of was to have a disjunction of high skilled and low-skilled employees. But that appears to be rather inconvenient.

enter image description here

Best Answer

Here's one idea:

CREATE TABLE Employees
( empno ... not null primary key
, skilllevel ... not null
);

CREATE TABLE Supervising
( supervisor_empno ... not null references Employees (empno)
, empno ... not null references Employees (empno)
, primary key (supervisor_empno, empno)
);

If you want to guarantee the skilllevel in supervising:

CREATE TABLE Employees
( empno ... not null primary key
, skilllevel ... not null
, unique (skilllevel, empno)
, check (skillevel in ('High', 'Low'))
);

CREATE TABLE Supervising
( supervisor_empno ... not null 
, supervisor_skillevel ... not null
,    foreign key (supervisor_skillevel, supervisor_empno)
     references Employees (skillevel, empno)    
, empno ... not null references Employees (empno)
, empno_skillevel ... not null
,    foreign key (skillevel, empno)
     references Employees (skillevel, empno)    
, check (supervisor_skillevel = 'High')
, check (skillevel = 'Low')
, primary key (supervisor_empno, empno)
);
Related Question