8

Consider the following ternary relationship: ternary relationship

Suppose that all entities have only two attributes (PK and Name).


Here are the tables I derived (5 tables):

Sector
-------------------------
ID_Sector    SectorName
-------------------------

Product
-------------------------
ID_Product    ProductName
-------------------------

Company
--------------------------------------
ID_Company    ID_Sector    CompanyName
--------------------------------------

Relationship 1 (R1)
-------------------------
ID_Sector    ID_Product
-------------------------

Relationship 2 (R2)
-------------------------
ID_Company    ID_Product
-------------------------

Question:

Is it a good solution for that ternary relationship? What's the difference between having 2 tables (R1 and R2) instead of the following single table:

Ternary table
-------------------------------------
ID_Sector    ID_Company    ID_Product    
-------------------------------------

To me, it looks like that having 2 separate tables for each relationship (R1 and R2) is a better solution when compared to having a single table, but I don't know if that's actually true or if it's a good practice.

6

The two solutions model different rules. With the ternary table you are saying that a company may only have certain products in a particular sector. In a different sector there will be a different set of products, though the two sets may overlap, of course.

With the binary tables you are stating that sector has no influence over what products a company my relate to. Similarly the company has no influence over what products are in which sector.

The choice between these alternatives will be determined by your business rules. It cannot be answered by an abstract, academic discussion. I have found it best to name the relationship between the entities. Saying that company is related to product, say, is interesting; saying why company is related to product is even better. "Company buys product" is a different piece of information to "company makes product" or "company does not have security clearance to use product". By doing this I often discover new relationships, attributes and entity types. You may end up needing both the binary and ternary tables!

Edit: For the rules

  1. a Company produces many products / each product is produced by exactly one company
  2. a Company reported in exactly one sector / each sector reports on many companies
  3. a Product is sold in exactly one sector / each sector has available many products.

I would have these entity types

Sector - SectorID

Company - CompanyID, SectorID

Product - ProductID, CompanyID

If any of your rules are many-to-many then you'll need the binary association tables.

As an aside, the relationship names "has", "belongs to" and "is a" often hide more than they illuminate. If you find your BAs using these ask them to have another think.

  • Let's suppose the business rules are: 1) Companies produce products; 2) Companies belong to a sector (examples of sector: machinery, food, software); 3) Products belong to sectors. I tried to be concise and paid the price of hiding important information. Thank you! – feelthhis Apr 24 '14 at 17:32
  • My comment raises another question: Which business rules does the ERD above model? Is there a way to make the ERD very precise regarding the business rules? Does the ternary table accurately represent the ERD above? If so, then the ERD above is the wrong model with regards to the stated 3 business rules, right? – feelthhis Apr 24 '14 at 17:45
  • Which business rules does the ERD above model: the ternary table would mean that there is a thing which has ProductID, SectorID and CustomerID as the necessary and sufficient key. Something like "GE (company) finances (Product) 90% (attribute) of all aero engines (sector)" whereas "Goldman finances 5% of aero engines" and "GE finances 3% of windmills". – Michael Green Apr 25 '14 at 12:29
  • @feelthhis - "Is there a way to make the ERD very precise regarding the business rules": yes! That's what ERDs do. I'll edit my answer. "Does the ternary table accurately represent the ERD above": I think you mean "rules above". No. A Product is made by exactly one company. A company belongs to exactly one sector. Knowing ProductID one can uniquely identify customer and sector and these IDss would be redundant in the ternary table. Hence it is not normalised according to your 3 rules. "If so..": I agree. – Michael Green Apr 25 '14 at 12:41
  • Ternary Table: a company may... Using the notation (IDSector, IDCompany, IDProduct), does it mean that the tuples (1, 1, 1) and (1, 1, 2) are allowed ("c1" produces "p1" and "p2" in "s1"); and that the tuples (1, 1, 1) and (2, 1, 2) are not allowed ("c1" produces "p1" in "s1" and produces "p2" in "s2")? Why? Shouldn't the ternary table allows any possible tuple (IDSector, IDCompany, IDProduct)? Binary Tables: sector has...; company has... I though that the ternary table was equivalent to the binaries, if the ternary table allows any tuple (IDSector, IDCompany, IDProduct). – feelthhis Apr 29 '14 at 0:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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