CODEWITHSUNDEEP
attributesentity-relationshiperdentities
Millennial
Millennial

Reputation: 89

Can a relationship be formed between more than 3 entities?

I am currently working on entity relationship diagram for a database and in that, there's an account and that account A and that can be of types say A1, A2, A3, A4, A5. Initially all of them are of type A and can be converted to A1 or A2 or A3 or A4 or A5 depending on the user's choice after verification.

I know about ternary relationships but here, I have more than 3 entities. How should I create a relationship among the 6 entities: A, A1, A2, A3, A4, A5?

Suggest me some ideas or provide me some reference I can refer to. I read a few articles but couldn't find any leads. Thanks.

Upvotes: 0

Views: 2112

Answers (1)

Christophe
Christophe

Reputation: 73500

In short

Yes you can, but not in all notations and it's not a good idea for your needs.

More arguments

It's possible

Peter Pin-Shan Chen, the inventor of E/R modelling has defined in one of his foundational papers that relationships can relate more than two entities:

Entities are related to each other. Different types of relationships may exist between different types of entities. A relationship set is a set of relationships of the same type. For example, PROJLEMP, which describes the assignment of employees to projects, is a relationship set defined on two entity sets, EMP and PROJ. A relationship set can also be defined on more than two entity sets. For example, PROJ_SUPP_PART is a relationship set defined on three entity sets PROJ, SUPP , and PART. In the entity-relationship diagram, a relationship set is rep- resented by a diamond-shaped box with lines connecting to the related entity sets.

But not without complications

However this comes with two practical constraints:

  • Not all notations support it: The Chen notation uses a special symbol for relationship and supports this theory. Unfortunately, the more frequent Crow's foot notation for ERD doe not support n-ary relationships, since each relationship is represented by a line that has only two ends.
  • It's ambiguous in your case: Chen notation allows to distinguish for the entities on total (must) or partial (can) participation to a relationship. Unfortunately, this becomes ambiguous with more than one partial participation in a relationship. Moreover, there would be no way to say that an A has a relationship with only one of the A1...A5 and not several at the same time

A simpler solution

A simpler solution, would be to put all the common attributes into A, and leave in A1.. A5 only the additional attributes. You would then represent the relation ship between A and An as a weak one, with a partial participation of An.

The advantatge is that you can represent this in every notation and that it's simple and unambiguous to read. It wouldn't prevent multiple relationships, so you would have to add a comment to clarify.

A sophisticated solution

Another approach is to reach out for EERD notation, and realise that A1..An are specialisations of A. You would represent this with an IsA relationship. The advantage is that it is loyal to your design and clearly explains what you mean in your narrative.

There are however several inconveniences:

  • Not all modelling tools support EERD.
  • EERD is not standardized, and there are in fact several variations. A more robust choice would be to opt for UML class-diagrams that are standardized and support unambiguously the different solutions exposed here.
  • Finally, RDBMS do not support IsA. So you would have your conceptual model fine but still have to map it to database tables outside of the model using one of the typical three patterns (single table, class table, and concrete class table).

Upvotes: 0

Related Questions