0

well I've posted this question originally on StackExchange for database adminsitrators: https://dba.stackexchange.com/questions/28356/should-this-be-an-identifying-relationship-or-not

But it seems to lack users I guess. So can anyone help me with this?

Edit: Alright, I chose to have a non-identifying relationship, this way the User can be Patient, SpiProfessional or both. Seems to work better, even though it's more work when writing queries. Thanks for everybody's answers, they all contributed to my understanding of databases.

Bhargav Rao
  • 50,140
  • 28
  • 121
  • 140
Arturas M
  • 4,120
  • 18
  • 50
  • 80
  • 1
    After clicking through multiple links, it is clear that the cardinalities on your diagram are not yet accurate. As a for instance, you have a 1:1 relationship between SpiProfessional and Holiday. That implies every SpiProfessional has exactly one Holiday in the database at any given time. You also have a 1:1 relationship between User and Patient, so every user is a patient; you also have a 1:1 relationship between User and SpiProfessional, so every user is also an SpiProfessional (and, by inference, every SpiProfessional is also a Patient). – Jonathan Leffler Nov 08 '12 at 04:35
  • @JonathanLeffler Exactly, I had first unidentifying relationship, then changed them to identifying and noticed that they now are necessarily both and it's hard to distinguish the type. So it should be unidentifying? Any suggestions how we could then know whether the user is a patient and whether he might be also an SpiProfessional? – Arturas M Nov 08 '12 at 04:41
  • You must know the rules for the domain that you're modelling. We can't tell you; most designs could be correct for some domain, but which one is correct for yours depends on the rules in your bit of the real world. That said, it is unlikely that the SpiProfessional to Holiday relationship is correct. An SpiProfessional might exist in the database without any holiday records, and an SpiProfessional might well have several holidays scheduled or recorded. But you have to write down the rules about what all the entities mean, and when you have that clear, the rest usually falls in place. – Jonathan Leffler Nov 08 '12 at 04:50
  • 1
    At a guess, you can have users who are neither Patients nor SpiProfessionals. You can mandate that every SpiProfessional has a record in the User table; ditto for every Patient. That means there is an optionality about the relationship between User and Patient and between User and SpiProfessional. It is not clear whether someone can be a Patient and an SpiProfessional at the same time; there is nothing that says a doctor can't break a leg and become a patient while remaining a doctor, for example, so the answer is probably "Yes, a user can be both, but usually is one or the other". – Jonathan Leffler Nov 08 '12 at 04:53
  • @JonathanLeffler Exactly, since a doctor can break a leg, he might also be a patient. Now whether user exists without being one of them. Not sure, maybe, we could decided to add administrator later, but not necessarily in this scope. But do you think the relationship should be identifying from user to patient and from user to spiProfessional? – Arturas M Nov 08 '12 at 04:59
  • I'm not certain what connotations 'identifying relationship' is supposed to have; it isn't a piece of jargon I've come across before. That makes it difficult to pontificate confidently. However, it is likely from the general meanings of the terms that yes, those relationships are identifying relationships. – Jonathan Leffler Nov 08 '12 at 06:01

2 Answers2

2

Need to know more information in order to answer your question.

  1. Can a user be Patient?
  2. Can a user be a SpiProfessional?
  3. Can a user be a Patient and a SpiProfessioanl?
  4. What additional data/attributes need to be stored for the patient?
  5. What additional data/attributes need to be stored for the SpiProfessional?
Lynn
  • 241
  • 2
  • 2
  • 3a. Must a user be a Patient or an SpiProfessional or can users exist who are neither? – Jonathan Leffler Nov 08 '12 at 04:46
  • @Lynn thanks for taking attention at my question 1. Yes. 2. Yes. 3. Well I think it would be best if he could be both, yes. 3.a. Well I'm not that sure because of that, but maybe there should be a theoretical possiblity be left for administrators, etc.? 4. In Patient just the additionalData varchar 5. The SpiProfessional doesn't contain anything by itself, but other tables have links to him, like his holiday, his professions, his workschedules for a particular profession. I've also added a link from the visits table, though in this old image it's not visible yet. – Arturas M Nov 08 '12 at 04:47
0

OK, here is what I think based on your design. The User table and Patient table, it shouldn't be 1 to 1 relationship, as the user may not be a patient, so it should be 1 to 0..1 relationship.

The same goes with SpiProessional. The user may not be a SpiProfessioanl, so the User table to Spiprofessional, should be 1 to 0..1 relationship.

I think it's worth to have a look at these two post. 1. Any example of a necessary nullable foreign key? 2. Implementing one-to-zero-or-one relation in SQL Server

Community
  • 1
  • 1
Lynn
  • 241
  • 2
  • 2
  • Welcome to Stack Overflow. Generally, you should write one answer to a question; you can edit your own answers and questions (multiple times if need be) even when you're a complete newcomer to the site. (You can also delete your own answers.) – Jonathan Leffler Nov 08 '12 at 05:59
  • Ok, Cool. Sorry didn't realize. Still a newbie. Thanks for letting me know. – Lynn Nov 08 '12 at 06:09
  • There is no unique solutions for database design, it all depends on what you want to achieve. Logically, it is best to create a person table separately to store the common attributes for Patient and SpiProfessional, but in consideration of the performance and maintenance purpose, it would be better to remove the person table and store these attributes in Patient and SpiProfessional table. – Lynn Nov 16 '12 at 05:18