How would you model this database relationship?

Question

I'm modeling a database relationship in django, and I'd like to have other opinions. The relationship is kind of a two-to-many relationship. For example, a patient can have two physicians: an attending and a primary. A physician obviously has many patients.

The application does need to know which one is which; further, there are cases where an attending physician of one patient can be the primary of another. Lastly, both attending and primary are often the same.

At first, I was thinking two foreign keys from the patient table into the physician table. However, I think django disallows this. Additionally, on second thought, this is really a many(two)-to-many relationship.

Therefore, how can I model this relationship with django while maintaining the physician type as it pertains to a patient? Perhaps I will need to store the physician type on the many-to-many association table?

Thanks, Pete

Answer 1

I agree with your conclusion. I would store the physician type in the many-to-many linking table.

Answer 2

Consider using a many-to-many join table. Use application logic to prevent more than two physicians per patient.

Physician
    Physician_ID
    ...

Patient
    Patient_ID
    ...

Physician_Patient
    Physician_ID int not null
    Patient_ID int not null
    Type ENUM ('Primary', 'Attending')
    PRIMARY KEY (Physician_ID, Patient_ID)
    KEY (Patient_ID)

Answer 3

How about something like this:

class Patient(models.Model):
    primary_physician = models.ForeignKey('Physician', related_name='primary_patients')
    attending_physicial = models.ForeignKey('Physician', related_name='attending_patients')

This allows you to have two foreign keys to the same model; the Physician model will also have fields called primary_patients and attending_patients.