title: Database design for a recursive relationship
body: <p>Consider this case where I'm trying to model a database for a company:</p>
  <ul>
  <li>Entities: <code>Employees</code>, <code>Managers</code>, <code>Departments</code>.</li>
  <li>An <code>Employee</code> works in only 1 <code>Department</code> while a <code>Department</code> may have many <code>Employees</code> working in it.</li>
  <li>A <code>Manager</code> may manage only 1 <code>Department</code> and similarly a <code>Department</code> may have only 1 <code>Manager</code>.</li>
  <li>A <code>Manager</code> supervises many <code>Employees</code>, but an <code>Employee</code> is only supervised by one <code>Manager</code>.</li>
  </ul>
  <p><strong>Now I have 2 ways to model this:</strong></p>
  <p><em><strong>First solution:</strong></em></p>
  <p>I'll consider that the <code>Manager</code> entity inherits from the <code>Employee</code> entity considering that I'll keep data that is unique to the Managers (e.g. Bonus &amp; Status).</p>
  <p><img src="https://i.stack.imgur.com/DzIwq.png" alt="First Solution" /></p>
  <ul>
  <li><p>Since the relation between <code>Department</code> and <code>Employee</code> is <code>1:N</code> then I'll put the
  <code>Department Id</code> as a foreign key in the <code>Employee</code> table for the <code>Works</code>
  relation.</p>
  </li>
  <li><p>Since the relation between <code>Department</code> and <code>Manager</code> is <code>1:1</code> then I'll put the
  <code>Department Id</code> as a foreign key in the <code>Manager</code> table for the <code>Manages</code>
  relation.</p>
  </li>
  </ul>
  <p><strong>Problem: How can I represent the recursive relation between the <code>Manager</code> and <code>Employee</code>?</strong></p>
  <hr />
  <p><em><strong>Second solution:</strong></em></p>
  <p>I'll consider that the <code>Manager</code> entity is not needed as other <code>Employees</code> may also have a <code>Bonus</code> and <code>Status</code>. (Actually I added these 2 attributes just to see how to model it in both cases)
  <img src="https://i.stack.imgur.com/Q6PpW.png" alt="Second solution" /></p>
  <ul>
  <li>Since the relation between <code>Department</code> and <code>Employee</code> is <code>1:N</code> then I'll put the
  <code>Department Id</code> as a foreign key in the <code>Employee</code> table for the <code>Works</code>
  relation.</li>
  <li>Since the relation between <code>Employee</code> and <code>Manager</code> is <code>1:N</code> then I'll put the
  <code>Employee Id</code> as a foreign key in the <code>Employee</code> table for the <code>Supervises</code>
  relation and call it <code>Manager Id</code>.</li>
  </ul>
  <p><strong>Problem: How can I represent the relation between the <code>Manager</code> and <code>Department</code>?</strong></p>
  <hr />
  <p>Questions:</p>
  <ol>
  <li>Is there any obvious mistakes in both design as they are?</li>
  <li>How to solve each problem in both cases?</li>
  <li>Is there a better solution than these two?</li>
  </ol>