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.

  • Please edit your question so there's only one question. Right now it's running the risk of being closed as "too broad". Which alternative design to take is going to be a matter of opinion; we can only help you with the technical possibilities. My answer shows a direction you might be able to go. May 24, 2018 at 15:29
  • @Colin'tHart you mean i remove the second question ? May 24, 2018 at 16:00

1 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.
  • ok , does that makes the relationship between treatments and patient-records many-to-many with a new table or relationship between them? May 24, 2018 at 15:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.