My professor (who claimed to have a firm understanding about systems development for many years) and I are arguing about the design of our database.
As an example: My professor insists this design is right: (list of columns)
Subject_ID Description Units_Lec Units_Lab Total_Units
etc…
Notice the total units column. He said that this column must be included. I tried to explain that it is unnecessary, because if you want it, then just make a query by simply adding the two.
I showed him an example I found in a book, but he insists that I don’t have to rely on books too much in making our system. The same thing applies to similar cases as in this one:
student_ID prelim_grade midterm_grade prefinal_grade average
He wanted me to include the average! Anywhere I go, I can find myself reading articles that convince me that this is a violation of normalization. If I needed the average, I can easily compute the three grades. He enumerated some scenarios including (‘Hey! What if the query has been accidentally deleted? What will you do? That is why you need to include it in your table!’)
Do I need to reconstruct my database(which consists of about more than 40 tables) to comply with what he want? Am I wrong and just have overlooked these things?
Another thing is that he wanted to include the total amount in the payments table, which I believe is unnecessary. (Just compute the unit price of the product and the quantity.) He pointed out that we need that column for computing debits and/or credits that are critical for the overall system management, that it is needed for balancing transaction. Please tell me what you think.
Advertisement
Answer
You are absolutely correct! One of the rules of normalization is to reduce those attributes which can be easily deduced by using other attributes’ values. ie, by performing some mathematical calculation. In your case, the total units column can be obtained by simply adding.
Tell your professor that having that particular column will show clear signs of transitive dependency and according to the 3rd normalization rule, its recommended to reduce those.