1

The question title is very long, so here's a little bit of context to (try to) make it clear.

I asked a question regarding ternary relationship. The question was Ternary relationship: what's the difference between having single table and having multiple tables?

It was very well answered by @Michael Green but it raised me another question, which follows.

Consider the following ERD: ERD

Consider the following business rules:

  1. Companies produce products
  2. Companies belong to a sector (examples of sector: machinery, food, software)
  3. Products belong to sectors

Questions

  • Does the ERD above accurately model the business rules?

  • Is there a way to make the ERD very precise regarding the business rules?

  • Does the ternary table accurately represent the ERD above? (for that question, please refer to the parent question). If so, then the ERD above is the wrong model with regards to the stated business rules, right?

Community
  • 1
feelthhis
  • 265
  • 1
  • 3
  • 10

1 Answers1

1

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.

Michael Green
  • 25,255
  • 13
  • 54
  • 100