Ternary relationship: what's the difference between having single table and having multiple tables? Consider the following 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.
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


a Company produces many products / each product is produced by exactly one company
a Company reported in exactly one sector / each sector reports on many companies
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.