Double-reference the same table in another table

Question

Double-reference the same table in another table

Navigation

#1 by (1 votes)

1

My question is this: I have to design a system for a health care institution that has internal physicians. There are users, and each user has an internal doctor. Each patient also has a family doctor of the SNS.

A doctor can be an internal doctor and a family physician, a patient may have doctors of different types and in some situations the same doctor can be both types to the patient.

Draw the following image of the options that occur to me. Which do you think is best to implement? Any suggestions other than those presented?

Thank you

php mysql sql

asked by anonymous 25.03.2015 / 20:52

1 answer

Use prepared statements and bound values to avoid SQL Injection with PDO? Check random numbers (randint)

score 1 · Answer 1

I frankly would not complicate it so much and implemented like this:

A doctor can have multiple clients [check]

A user can have multiple doctors [check]

A doctor can be associated with a user as an inmate and / or family member [check]

For this association uses the designation field with the values (Internal, Family, Both)

And imagine, that the patient needs special care of a doctor outside the institution and is not the family doctor, with this scheme you can easily make this association without having to modify the database!