Should this be an identifying relationship or not?

Question

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

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...

Answer 1

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.