Ternary Relationship in Database

In a Ternary Relationship, three different entities participate in a single relationship simultaneously. The relationship degree is 3. When determining cardinality, we consider it in the context of two entities relative to the third.

Example: Mobile Manufacturing Company

Consider a mobile manufacturing company with three entities −

• Mobile − The mobile models manufactured by the company.
• Part − Mobile parts which the company gets from suppliers.
• Supplier − Suppliers who supply mobile parts to the company.

All three entities participate simultaneously in a SUPPLIES relationship −

Cardinality in Ternary Relationship

To determine cardinality, fix two entities and ask how many instances of the third can participate −

Mobile Cardinality (N)

Mobile (N) − For a given Supplier S1 and Part P1, can S1 supply P1 to multiple mobile models? Yes. For example, Supplier S1 supplies Processor P1, and the company uses P1 in multiple models like iPhone 14, iPhone 15, etc. So the cardinality of Mobile relative to Supplier and Part is N (many).

Supplier Cardinality (M)

Supplier (M) − For a given Mobile M1 and Part P1, can P1 be supplied by multiple suppliers? Yes. For example, Mobile M1 has Part P1 (like camera sensor) being supplied by multiple suppliers for redundancy. So the cardinality of Supplier relative to Mobile and Part is M (many).

Part Cardinality (P)

Part (P) − For a given Supplier S1 and Mobile M1, can S1 supply multiple parts for M1? Yes. For example, Supplier S1 supplies screen, processor, battery, etc. for Mobile M1. So the cardinality of Part relative to Supplier and Mobile is P (many).

Conclusion

A ternary relationship involves three entities participating simultaneously, with cardinality determined by fixing any two entities and examining how many instances of the third can participate. This results in M:N:P (many-to-many-to-many) cardinality in most real-world scenarios, making ternary relationships complex but essential for modeling real business processes.