0

I am trying to create an Entity/Relationship Chen Diagram for a new APP. Scope is simple enough.

I will have multiples types of users login through the same Login Form.

What I decided to do... Is create a parent Table call Credentials that will contain only the Username/Email & Password.

First Draft

Users, Subcontractors, Clients & Employees will have access to the system but each of those tables will have diferents attributes, that's why I need differents table. But all of them will have a Username/Email & Password.

I guess that is a Hierarchy relationship. And the relations should be I guess...

  • 1 Credentials to 1 Users.
  • 1 Credentials to 1 Subcontractors.
  • 1 Credentials to 1 Clients.
  • 1 Credentials to 1 Employees.

I am doubting a lot.

  1. How can I represent that in a E/R properly?, Is this way a good way to do it?
  2. In children tables Should I use the Credentials ID as primary key? or is better to use Credentials ID as foreign key but have primary key as well?
  3. Children... are week entities right?
  4. If for example... a Subcontractor will required differents account to login... different should be... create a NM?
2
  • Getting your E/R diagram right is less important than getting your database design right. The E/R diagram is just there to help you and your team visualize the database design. Jun 27, 2020 at 17:59
  • Accordingly, your current diagram is perfectly adequate if your team understand that the triangle represents your inheritance relationship. Jun 27, 2020 at 18:00

1 Answer 1

1

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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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