Prelimanry remarks
There is no semantic defined for generalization/specialization relation and no triangle in the graphical notation either (at least in the original version of the most popular E/R notations I know).
Your construct has therefore to rely on ordinary relationships. And this may be a good thing, because what you see as a simple hierarchy might well be a network of relations:
- Isn't it possible that a
Subcontractor of your company in one area, is also a Client of your company in another area? - Isn't an
Employee also a User or that a third party User represents a Subcontractor and/or a Client? - Is the relationship of
Credential with User or Employee really the same as with Client of Subcontractor? Including regarding cardinality? And is it Credentials that should be related to a Subcontractor or is it User ?
These questions might force you to reconsider your problem completely. But for the sake of building a future-proof model, I encourage you to do so. At first glance, I can feel some serious flaws in your current approach. This being said, I'd like to answer your questions.
1. How to represent this relation in E/R?
There are two ways to represent this relationship: either 4 binary relationships or one n-ary relationship. In all cases, the relationship is optional for the 4 entities at the bottom.
The n-ary relationship has the advantage of being closer to your narrative, since there is conceptually only one relation, in which only one of the entity at the bottom is expeted to participate. There are however a couple of drawbacks:
- There is no graphical way to convey the information that only one entity of the bottom can participate in one specific relation.
- At database level, the relations between tables don't account for n-ary relations either.
The alternative with 4 binary relations has other drawbacks. But it has one advantage: it models a world in which subcontractors and customers are considered indepenently, and both could have occurences with a relation with the same credential. This apparent drawback in regard of your intent could in fact appear to be an advantage in view of my preliminary remarks.
2+3. Identity and weakness
The E/R modelling theory considers that each entity shall have an identifier. It is however a common modelling practice to have a dependency relationship in which the identity of the depending entity depends on its dependent. Let's quote Chen in his seminal paper:
If some entities in the relationship are identified by other relationships, we shall call it a weak relationship relation.
This works also in a 1:1 relationship where the depending entity has no own identity. So, you could in theory reuse for the children the identity of Credential (the primary key would also be a foreign key), making these weak entities (i.e. not independent).
However, considering my preliminary remarks, I would strongly advise against it and to prefer having each of the four entities to have their own unique identifier.
4. Evolution of the model?
So far we've understood Subcontractor as being a Subcontractor Credential or a Subconnractor user.
If you come to the conclusion, that in fact there could be one subcontractor, with several users (names, emails), your initial modelling goes the wrong way and my preliminary remarks are confirmed.
In this case, make Subcontractor (and Client?) independent entities with their own identifier. The relation with credential would then be an ordinary 1:N (or M:N, depending on other requirements of the context) relation.
In the OOP, it is recommended to prefer composition over inheritance. Could it be translated into the relational world: prefer relations over hierarchies?
