Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Well I've attached the image to explain what I need to know:

enter image description here

I thought it was supposed to be an identifying relationship, but now I kinda thought maybe it wasn't? The thing is the user won't necessarily be both: the patient and the professional? So how do I recognize which one he is or whether he could be both(if that's possible)? Should Patient and SpiProfessional have additional primary keys of their own? I've kind of got confused on this one. I was also thinking of maybe adding one additional field to recognize whether the user will be a patient or an spiProfessional, but then again you might want to add other types later and then the user would only be limited for one type...

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

Patient and SpiProfessional are uniquely identified only by their relationship to User, which means they are in an identifying relationship. It can also be helpful to think of them as "depending" on User.

That was the original question. Then there were two design questions:

So how do I recognize which one he is or whether he could be both(if that's possible)?

Without knowing more about the application's requirements or application logic, it seems that information can be determined by simple reports.

For example, to find Patients who are not also pros:

SELECT u.* , p.*
FROM User u 
INNER JOIN Patient p 
ON u.idUser = p.idUser
LEFT OUTER JOIN SpiProfessional sp
ON  u.idUser = sp.idUser 
WHERE sp.idUser IS NULL

If you do not care about the User only being one type, then cut off the last three lines. In any case, the best way to do this depends on your application requirements.

Should Patient and SpiProfessional have additional primary keys of their own?

It's becoming a popular practice to give every table a single column primary key. I like doing this, but it is a preference.

See also this post on StackOverflow.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.