Reference the same table twice in another table

Question

Reference the same table twice in another table

Asked 9 years, 9 months ago

Viewed 154 times

1

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

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

I have drawn the following picture of the options I can think of. Which do you think is best to implement? Any suggestions other than those presented?

inserir a descrição da imagem aqui

Thank you

But all structures seem to be totally different from each other, personally I would go on the first n:m (many for many). But you can’t be sure which goal, because they all seem to have different purposes (at least for me)

– Guilherme Nascimento

2015/03/25 at 20:35

1 answer

Browser other questions tagged php mysql sql

You are not signed in. Login or sign up in order to post.

by Ricardo Cruz • **162** points · Answer 1 · 2015-03-25T23:42:26+00:00

I frankly wouldn’t complicate so much and implement so: inserir a descrição da imagem aqui

A doctor may have several users [check]

A user can have several doctors [check]

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

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

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