One (optional) to Many [recursive]

Diagram
How to read	One employee may manage many employees. Each employee may be managed by a manager.
Relevant relations	The many side of this relation can be either optional or mandatory.

The table structures are as follows:

employee(__id__, name, <e_other>)
manage(__emp_id__*, mgr_id*)

The SQL create statement is as follows:

create table employee
  (id type primary key,
   name type,
   <e_other>);
create table manage
  (emp_id type primary key,
   mgr_id type not null,
   foreign key (mgr_id) references employee,
   foreign key (emp_id) references employee);

The reader should note that mgr_id cannot be the primary key of manage because many employees can have the same manager.

Back to ER to SQL page.