This essay is part of the Keys, Normalization, and Denormalization collection. There are also a complete Table of Contents and a Skills-oriented Table Of Contents. Update: There’s a new essay on Business Logic that also goes into how calculated values have an effect on your code paradigm. Third regular form is fairly simple. In plain phrases it signifies that no column can rely on a non-key column.
The example below exhibits a desk that’s not in third normal kind. Student, there aren’t any duplicate values of this mixture of columns. In plain terms we mean that no student might be listed in the identical class twice, which only makes sense in any case. We word subsequent that there is only one actual property of the table, the GRADE column.
This is the pupil’s final grade in a specific class. The violation is the last column, LETTER, which isn’t a property of the important thing however is functionally dependent upon the GRADE worth. The time period “functionally dependent” signifies that column LETTER is a operating of GRADE. As always, normalization is meant to assist us.
The issue with the desk as-is is that you can’t assure that when the GRADE column is updated that the LETTER column shall be correctly updated. The non-normalized desk can lead to what we name Update ANOMALIES. We now have seen in prior essays that imposing first normal kind and second normal kind results in more tables and simpler code. We noticed in each that a programmer’s pure tendency to use an “easy” answer of fewer non-normalized tables results in extra difficult and error-prone code. But once we come to the third normal type the advantages do not seem so clear-reduce.
Third regular form tells us to go away out the LETTER column, but the plain reality is that the LETTER column could be very useful! The situation will solely get worse if there are a couple of dozen (or just a few hundred!) tables. If we leave out all calculated values then we should present for them outdoors of the database, but when we put them into the tables we threat all sorts of errors in stay knowledge.
That is the place issues get sticky. In this week’s essay I am going to elucidate the classic options to this problem, and include my very own because the final one. It’s my private opinion that the problem of calculated values is at the heart of most of the flame wars, arguments, and battles that are fought in the arena of database utility growth.
- Loads of circumstances of abuse, excessive useful resource usage and blacklisting
- Establish Clear Reporting Methods
- Double-click the License icon
- Demonstrates an skill to successfully manage several duties at once
You can’t stay with them however you cannot dwell without em. Some type of logical strategy is required that offers us the benefits of normalization and the advantages of calculated values. In many instances something that appears like a calculation can really be eliminated out to a different desk and turned into an international key.
Most programmers wouldn’t consider a solution like this as a result of it has too many rows! Your fundamental programmer finds it ugly, so he won’t use it. This underscores a recurring theme of this collection: skills that lead to good coding will lead to unhealthy table design. Table design needs to be accomplished in line with the rules of table design, not the ideas of coding. The customer won’t ever know you’ve an “ugly” table, they are going to simply know your system works well. There are certain benefits that you simply always get once you normalize and when you set every part into tables. The large benefit is that you would be able to change your system without modifying a program file.