Should ERD derived attributes be included in logical tables

database-designerd

I have created my entity-relationship diagram (ERD for brevity) and it has derived attributes.

When it comes to database design using a relational schema, I've designed my tables and linked everything properly.

I don't know if I should add a column to my table that has a derived attribute? For example, the derived attribute is calculated as follows:

  • Total = Price * Quantity

And it is related to the following illustrative table schema:

Customer (
    ID, 
    CustomerName, 
    ItemsID, 
    Total
);

Should Total be included in the Customer table?

Best Answer

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.

Related Question