Paradoxo44 Paradoxo44 - 13 days ago 7
SQL Question

Doctors, Patients and Contact information for both

I have two tables

DOCTORS
and
PATIENTS
. 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.

I thought about creating a separate table e.g.
PHONES
with fields
phoneID
, the phone number and a foreign key that points to a contact, such as below:

PHONES

phoneID [pk]
number
contactID [fk]


DOCTORS

docID [pk]
fname
sname
specialization
.
.
.


PATIENTS

patID [pk]
fname
sname
.
.
.


The first problem comes from the fact that the
patID
and the
docID
might (and will eventually) have the same value. So relating a phone to one and only one person becomes more difficult.

So far I've thought three possible solutions:


  1. 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.

  2. 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.

  3. Create separate
    PHONE
    tables for doctors and patients, but that's even more clumsy.



Somehow I think both approaches are not the best. Any advice?

Answer

You could introduce a PERSON-table. This is 1:1 related to your doctors and patients (and later maybe to employees, suppliers, institutions, whatever). Let the contacts be related to this person-table.

Application code can model this with inheritance quite easily...

Within your person-table you keep some general information like DisplayName and PersonType (reference to a person-type-table with entries like Doctor, Patient and ...).

Keep this table slim...

If you have to choose out of your own ideas, I'd prefer the second. Keep them in one table and mark them with a type column. Avoid speaking keys...

Comments