Database Design – Ternary Relationship: Single Table vs Multiple Tables

What you can do is not always appropriate for the various usages of your relational model. If you were creating a data warehouse in order to analyze customer sales then derived attributes would be appropriate. I have done this for a summary table for a reporting tool. The query would have joined upwards of thirty tables with many aggregations such as sums and show all values an entity has had. A summary table, refreshed daily, listed derived attributes was a great solution for reporting.

For an online transaction processing database using derived attributes is not always the best solution.

For example: now your total is price * quantity

Next month management decides to implement a discount of 10% for customers who order more than $1000 in a calendar year. Your total column now looks inflexible.

Unfortunately the things you can say today that "will never change" such as Total = price * quantity are really an example of business logic. Business logic can change anytime in unexpected ways.

To continue with your example....if management institutes a discount and you have a customers table, orders table then all you have to do is add a discount table. Then you can create a view which encapsulates the business logic of the day to derive total sales. When the logic changes you can change the view much easier than recalculating the derived attributes that are fixed in a table.

And if you really want to be prepared you could store the changes in the business logic in a table in the database and cover off "Who, What, why". So if the Bob the Manager offers a discount and five years later Sue, the new manager, says "When did we start offering discounts and who authorized it?" you are a database star.

I am not sure if this is the right place to pose such questions. But here some remarks and questions anyway. They are too long for a comment.

• I don't understand the word representant. I did not find it at http://dict.leo.org/. Dou you mean representative?

• With telephones you mean phone numbers and with emails you mean email adresses?

• Why is telephone a weak entity? What is its identifying entity? For me it is not a weak entity. A phone extension may be a weak entity. The same holds for email and address.

• can a representant have more then one address? This is not excluded by the text but it is excluded by the ER diagtram.

• Why do you name some entities as singular and some as plural? I think a consisten notation will be more clear.

• Why do one need a reading direction? What is the difference if one reads the diagram from bottom to top? I see no difference.

• "if a representant is removed it must be saved in a list" That does not describe any relationships between a removed representant and the remaining entities. "it must be saved" is a term of computer programming and not of the business domain. It is not clear to me what you want but using an attribute to a relation may be not the appropriate solution. From your drawing I conclude a telephone is assigned to a representant. If the representant is removed the removed representant has a telephone too? If yes then this telephone cannot be used by then new representant for this address because a telephone has only one representant assigned.