Can i make a relationship between two relationships in the database?

Question

I am making a database system where there are patients, doctors, categories and medical records.

• doctor and patient has a relationship called treatments ( many-to-many )
• patient and a relationship with medical record called patient-records ( one-to-many )
• medical record and categories has relationship called record-category (many-to-many)

My questions:

How do I include the details and information of the medical record in the treatment relationship? Should I make a joined query and get the details of record from the patient?

My goal is to create a full record for the patient where all their history, diagnoses, and treatments are available as a sheet or web page.

Answer 1

Yes! You can make a new table which has foreign keys to both treatments and patient-records.

But be careful either in your model or in your code that you don't associate patient-records from a different patient with the patient associated with the treatment... using natural keys is one way to solve this:

• treatments has a primary or unique key (doctor, patient) -- Doctor and Patient can be surrogate keys -- and both of these are foreign keys to doctor and patient, respectively.
• patient-records has a primary or unique key (patient, medical-record) -- similarly, these are both foreign keys.
• your new table has a primary or unique key (doctor, patient, medical-record) -- and there are two foreign keys: one on (doctor, patient) to treatments, and the other on (patient,medical-record) to medical-records.