Ternary Relationship in Database Design – Understanding n-ary Relationships

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.

Answers

• The ERD shown does not match the rules stated.
• I believe your second ERD shows these rules.
• A ternary table would not match because of the many-to-many between sector and product.

Exposition

The rules you give are all binary rules. They relate one entity type to another. If you have a rule which mentions three entity type then a ternary table would be appropriate, but you have not. For example the intersection entity type "TargetMarket" would be ternary - Red Bull (company) targets energy drink (product) to software (sector).

I'm inferring from the many-to-many between company and product that the products are generic. For example "chocolate" and "spreadsheet". They cannot be "Toblerone" and "MS Excel" as they are trademarked and can be produced by only one company (ignoring licencing agreements). If all companies stoped producing chocolate (God forbid!) I imagine you would still like to record that "chocolate" was in the sector "food". With a ternary table this would not be possible. If all companies ceased producing chocolate (i.e. deleted corresponding rows from the ternary table) the chocolate <-> food association would disappear. Similarly a newly-formed company could not be recorded in this system until it was producing products. Should it ever cease producing products (e.g. become a shell company, go into administration) it would have to be removed from the ternary table and hence from the system entirely.

As I mentioned in the parent question, the full answer depends on the meaning of the relationships between the entity types, as embedded in their names, and both binary and ternary tables may be required. Say a company produces 4 products, each of which is categorised in 3 sectors. That would produce 12 possible sector-product-company combinations. If your system requires to capture that only, say, 9 of these possibilities are valid or exist in fact, or some other well-named constraint then the ternary table would be the right way to do this. But this is a rule which is not mentioned in your set above.

I would suggest you examine the constraints between a company's sector and those of the products it produces. There may be redundancies there which should be removed for the model.