2

I have the following problem that I have multiple scenarios that might be right or wrong, I've been searching on this for a while and I didn't find a specific answer for my problem:

Doctor Clinic Example:

We have doctor, patient, treatment, treatment-type

Doctor: id, name....

Patient: id, name...

Treatment: date, cost

Treatment-Type: id, name

Doctor can do multiple treatments, and Patient can also do multiple treatments, so they are connected with Treatment with(1-N) relationship.

Treatment entity is a weak entity, as it cannot be defined in the absence of Doctor or Patient, so my question is, when we convert this ERD to actual tables, which is the correct (or the best-practice) scenario?

1 - doctor-id, patient-id cannot define the Treatment table uniquely, so we add to Treatment table the treatment-id field, and the PK is (doctor-id, patient-id, treatment-id).

2 - We add treatment-id field, and the PK is(treatment-id).

3 - The PK will be (doctor-id, patient-id, date).

I struggled finding if 'date' can be part of PK or not, and also I struggled if I can create an unique ID for weak entity

Thanks in advance.

Mohammad Shahhoud
  • 469
  • 3
  • 6
  • 16

1 Answers1

2

Weak entity sets are entity sets that are partially identified by a parent entity set's primary key. A weak entity set necessarily depends on its parent entity set for existence (we say it participates totally in its identifying relationship), but not everything with an existence dependency is a weak entity set. Regular entity sets can also participate totally in one or more relationships. So, it depends on how you identify an entity set. See also my answer to the question "is optionality (mandatory, optional) and participation (total, partial) are same?"

An entity set that is uniquely identified by its own attributes is a regular entity set. An entity set that is partially identified by a parent entity set's primary key is a weak entity set. An entity set that is fully identified by a parent entity set's primary key is a subtype.

You should also note that weak entity sets can only have one parent entity set according to the entity-relationship model as Chen described it. Being identified by multiple parent entity sets would make it a relationship rather than an entity set.

In some schema design tools, a different interpretation is used where tables are equated to entity sets and relationships equated to FK constraints, and an identifying relationship would be an FK that is part of the PK of a table. This approach is closer to the network data model than the entity-relationship model, despite having adopted much of ER's terminology.

Let's take a look at your examples:

In example 1, we should consider whether treatment-id is identifying on its own (i.e. a surrogate key) or only in combination with doctor-id and patient-id (i.e. an ordinal number). If it's a surrogate key, it would be a mistake to include doctor-id and patient-id in the PK, example 2 would be the right way of handling it. If it's an ordinal number, then it's basically the same as example 3 - two foreign entity keys and a value set in a primary key. I'll say more about that in my comments on example 3.

In example 2, treatment-id is a surrogate key which means Treatment is a regular entity set which participates totally in its relationships with Patient and Doctor. This would be my recommended solution, since it's the simplest.

In example 3, you have a primary key consisting of two foreign entity keys and a value set.

The entity-relationship model doesn't cover such relations - relations with a single entity key are called entity relations, and relations with multiple entity keys are called relationships relations. Value sets are only described as the codomains of attributes, not the domains. The ER model's inability to handle arbitrary relations are a consequence of artificial distinctions between entity sets vs value sets, and between attributes vs relationships. Other data modeling disciplines like the relational model and object-role modeling are complete and can handle any kinds of relations.

Back to example 3, despite the ER model's shortcomings, it's not invalid to create such a table/relation in an actual database. However, think about what the primary key means - can a patient receive only one treatment per day from the same doctor? I would think multiple treatments should be possible, in which case you might need to add another ordinal number, e.g. (doctor-id, patient-id, date, treatment-id). In that case, it might be simpler just to do (doctor-id, patient-id, treatment-id).

One argument against such composite/natural keys is that they add up - a many-to-many association between two relations, each with 3 columns in their primary keys, could have up to 6 columns in its primary key! That gets inconvenient quickly, but on the other hand, those columns are relevant related info that would otherwise need to be retrieved from joined tables if the association was identified by a surrogate key.

Sorry about the long answer, but I hope this covers all the fine points. Let me know if you have any questions.

reaanb
  • 9,806
  • 2
  • 23
  • 37
  • 1
    Thanks, you helped me alot understanding the difference between weak and regular entities, but I have some more questions about your answer: _Being identified by multiple parent entity sets would make it a relationship rather than an entity set_ But my treatment table has more than one parent entity sets, but it's a weak entity, how is that possible? And if I worked on the example 2, then that would make Treatment entity as regular not weak, but actually it's a weak entity, isn't that wrong?
    Thank you very much for helping me sir
    – Mohammad Shahhoud Apr 02 '18 at 22:53
  • _But my treatment table has more than one parent entity sets, but it's a weak entity, how is that possible?_ In the standard entity-relationship model, it's not possible for a weak entity set to be identified by more than one independent parent entity sets. And if you work on example 2, then it actually becomes a regular entity set. – reaanb Apr 03 '18 at 15:48