title: Doctors, Patients and Contact information for both
body: <p>I have two tables <code>DOCTORS</code> and <code>PATIENTS</code>. I want both the doctors and the patients to have contact information (such as telephone numbers, addresses and so on) that aren't fixed. For example we can add multiple phones to either a doctor or a patient.</p>
  
  <p>I thought about creating a separate table e.g. <code>PHONES</code> with fields <code>phoneID</code>, the phone number and a foreign key that points to a contact, such as below:</p>
  
  <p><strong>PHONES</strong></p>
  
  <pre><code>phoneID [pk]
  number
  contactID [fk]
  </code></pre>
  
  <p><strong>DOCTORS</strong></p>
  
  <pre><code>docID [pk]
  fname
  sname
  specialization
  .
  .
  .
  </code></pre>
  
  <p><strong>PATIENTS</strong></p>
  
  <pre><code>patID [pk]
  fname
  sname
  .
  .
  .
  </code></pre>
  
  <p>The first problem comes from the fact that the <code>patID</code> and the <code>docID</code> might (and will eventually) have the same value. So relating a phone to one and only one person becomes more difficult.</p>
  
  <p>So far I've thought three possible solutions: </p>
  
  <ol>
  <li><p>Have custom format primary keys for the doctors and patients. For example doctors could have ids in the form of "d00001", "d00002" and so on, and patients ids like "p00001", "p00002". My concern is this could complicate things unnecessarily.</p></li>
  <li><p>Another solution would be to keep both doctors and patients in one table, and define if they are a doctor or a patient by using another field.</p></li>
  <li><p>Create separate <code>PHONE</code> tables for doctors and patients, but that's even more clumsy. </p></li>
  </ol>
  
  <p>Somehow I think both approaches are not the best. Any advice?</p>