Mysql – Making an ERD of a movie database

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 would use (title,prod_date) as the primary key, since movies are not uniquely identified by their title alone (remakes, for example). In my opinion, the first diagram is preferable, the sub-attribute approach in the second diagram seems a bit convoluted to me.