Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2010
    Posts
    1

    Unhappy Patient - complaint (terbary relationship question)

    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,

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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])
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •