Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

1 Answer
1

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.

Now the subtle ERD and Table differences (ternary vs binary) are very clear. As we agreed in the parent question, ERDv2 seems more appropriate. With "products are generic" you nailed it: the business rules are so that a Company produces "car" (instead of "Golf"), hence the N-M. Both Company and Productare categorized inSector (now I see the importance of using better wording!). If you wish to take a broader look at the ERD, I'd be glad to share with you. @Michael-Green you are helping me very much and I cannot express my appreciation enough, so I'll just say Thank you very much!
–
feelthhisMay 1 '14 at 21:52