Take the 2-minute tour ×
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.

I have designed my ERD. It has derived attributes. When it comes to relational data modeling using relational schema, I've designed my tables and linked everything properly. I don't know if I should add a field in my table that has a derived attribute?

Example:

Derived Attribute:- Total = Price * Quantity.
Customer (ID, CustomerName, ItemsID, Total)

Can "Total" be included in "Customer" table?

share|improve this question
add comment

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
    
Thank you for your response. Your explanation is very clear. You've helped me learn a lot. I shall do the needful, thanks :) –  deepz Nov 3 '12 at 21:35
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.