0

I am having a major issue with relating the following tables together. It is a many to many relationship between Patient and Nurse.

Here is the relational table:

Patient (PatientID (PK), Forename, surname, gender, date of birth, address, illness, prioirty)

seen_by (ID(PK) PatientID(FK to Patient.PatientID), NurseID(FK to Nurse.NurseID) )

Nurse (NurseID(PK), Forename, surname)

The issue I am getting is that, I want the PatientId to be assigned to a NurseID so I know what patient is seen by what nurse. Please note that the ID are all auto_increment values.

Any suggesions, thanks in advance!

user2027028
  • 17
  • 1
  • 7
  • you are confused, the id is autoincrement but it's also the UNIQUE identifier of a patient and a nurse. Your system will know who is nurse #9999 and it wont know who is 'Ramona Gonzales' – jcho360 Feb 13 '13 at 17:12
  • how do i correct this so I know which patient is assigned to which nurse? – user2027028 Feb 13 '13 at 17:15
  • if you make a query `where nurseID=123`, it will give you all the patients assigned to that nurse – jcho360 Feb 13 '13 at 17:17
  • I have tried that. The relationships between the joint table seen_by isnt correct so the query does not work. PatientID in the Patient table is a foreign key in the seen_by table and this relationship is fine. However, I need the patientid to relate to the nurse table. – user2027028 Feb 13 '13 at 17:25
  • If I am able to provide you with an email address, i can show you the relational tables via an image. – user2027028 Feb 13 '13 at 17:27
  • write down here your datastructure: http://www.sqlfiddle.com/#!2/61395/130 – jcho360 Feb 13 '13 at 17:28
  • Its more sufficient to show you an image. – user2027028 Feb 13 '13 at 17:33
  • You've already asked this question (http://stackoverflow.com/questions/14614309/creating-relations-between-tables-phpmyadmin) - please do not ask the same question multiple times. – Benny Hill Feb 13 '13 at 18:05

1 Answers1

0

Why not create a join table that would be the relationship between the patient and the nurse?

Table: Appointment (AppointmentID (PK), PatientID, NurseID)

Then you can assign the patient and the nurse to the appointment record and keep track of the relationship. This also gives you the benefit of keeping track of additional information regarding the appointment such as dates, prescriptions, etc.

I'm not sure what development environment you're using but Rails has some nice patterns for accessing objects through these kinds of relationships.

Richard Brown
  • 11,346
  • 4
  • 32
  • 43
  • the seen_by table is a join table. It has the same context as what your describing. I just cant relate it correctly to the patient and nurse table so that patientid and nurseid are assigned – user2027028 Feb 13 '13 at 17:23