Hi everyone, hope you'll doin' just fine.

I have a project about a hospital, which we are required to design a database for it .

anyway I have a ternary relationship between patient, complaint and treatment and it's many to many, so I'll have four tables.

Patient, Complaint, Treatment and Patient_Complaint_Treatment.

I was asked a question which is :

Display a list of patients and their complaints, treatments and dates of treatment.

to be in the picture, the date of treatment is in the treatment table < can you explain more.

so how can I write the SELECT statment to answer the question and which table should be used in ternary relationship in general ?


Thank very much,

You don't have a tertiary relationship. Not all treatments are applicable to all complaints.

You should have five tables:
Patient (PatientID)
Complaint (ComplaintID)
Treatment (TreatmentID)
ComplaintTreatment (ComplaintTreatmentID, [ComplaintID, TreatmentID])
Patient Treatment (PatientTreatmentID, [PatientID, ComplaintTreatmentID])