You raise actual points, however you did not fully understand normalization and what this means, for example, in
1) The statement that the maintenance of invoices, as they denormalize the data, is completely and completely erroneous. For example, let’s take a price - if you have a business requirement that says that you must save the price history, then saving only the current price is incorrect, and this violates the requirements. And this has nothing to do with normalization; it just is not well designed. Denormalization is the introduction of ambiguity into your model (and other artifacts) - in which case you simply do not model your problem space properly. There is nothing wrong with modeling a database to support temporary data (or versioning and / or dividing database areas into an archive / temporary and working set).
Looking at normalization, without looking at semantics (in terms of requirements), is impossible.
In addition, if your senior developer cannot see the difference, I think that he did not receive his seniority in the development of RDBMS;)
2) The second part is really denormalization. However, if you ever come across a senior BB analyst who seriously preaches normalization, you will hear him say that it’s quite acceptable to denormalize while you do this consciously, and make sure that overweight deficiencies and these anomalies do not bite you. They will also tell you to normalize the logical model and that in the physical model you are allowed to deviate from the ideal for various purposes (performance, maintenance, etc.). In my book, the main goal of normalization is that you have no hidden anomalies (see this article at 5NF )
Caching intermediate results is allowed even in normalized databases and even with the help of the largest normalization evangelists - you can do this at the application level (like some cache), or you can do it at the database level, or you can get a data store for such purposes . All of them are valid and have nothing to do with the normalization of the logical model.
In addition, as for your accountant, you should be able to convince him that what he claims is not a good test and develops a set of tests (maybe with him) that automate system testing without user intervention and give you more confidence that your system is not working.
On the other hand, I know systems that require users to enter duplicate information, for example, to enter the number of lines in the account before or after entering the actual lines, to ensure that the recording is complete. This data is "duplicated", and you do not need to store it if you have a procedure that will check the input. If this procedure comes later, it is allowed to store "denormalized" data - again, the semantics justify it, and you can look at the model as normalized. (it is useful to wrap your head around this concept)
EDIT: The term “denormalized” in (2) is incorrect if you look at the formal definition of normal forms and if you think a design is denormalized if it violates any of the normal forms (for some, this is obvious and there is no other way about this).
However, you can get used to the idea that many people and unnecessary useless texts will use the term “normalization” for any efforts that try to reduce redundancy in the database (as an example, you will find scientific documents in which I do not say that they should be right, just like the warning that this is a common occurrence that cause derived attributes as a form of denormalization, see here ).
If you want to appeal to some more consistent and recognized authorities (again, not recognized by everyone), perhaps the words CJDate may make a clear distinction:
Most of the design theory is associated with reduced redundancy; normalization reduces redundancy within relvars, orthogonality reduces its relvars.
qouted from Depth Database: Relational Relations Theory for Practitioners
and on the next page
as the inability to normalize the whole path implies redundancy and can lead to some anomalies, and non-compliance with orthogonality.
So the correct term for redundancy through relvars is orthogonality (basically all normal forms talk about the same reverb, so if you look strictly at normalization, it will never offer any improvements due to the dependencies between two different reliefs).
In any case, one of the other important concepts when considering database design is also the difference between logical and physical database models. Many things that are useful at the physical level, such as tables with subtotals or indexes, do not have a place in the logical model - where you are trying to establish and explore the relationships between the concepts you are trying to model. And that is why you can say that they are valid and they do not destroy the design.
Lines can sometimes be a little blurry in the logical model and physical model. A particularly good example is a table with subtotals. To consider it part of the physical implementation and ignore it at the logical level, you must:
- ensure that users (and applications) cannot update the subtotals table directly that do not match their predicate (in other words, there is an error in the test procedure)
- make sure that users (and applications) cannot update the table they depend on without updating the subtotal (in other words, some application will not delete a row from the details table without updating the total)
If you violate any of the above rules, you will receive an inconsistent database that will provide conflicting facts. (In this case, if you want to formally develop a procedure for fixing or studying the problems caused, you do not consider it simply an additional table, it will exist at a logical level, but it should not be there).
In addition, normalization always depends on the semantics and business rules that you are trying to model. For example, DBAPerformance gives an example in which storing TaxAmount in a transaction table is not a denormalized design, but it does not mention that it depends on which system you are trying to simulate (is this obvious?); for example, if a transaction has another attribute named TaxRate , it will usually be denormalized because there is a functional dependence on the set of non-key attributes (TaxAmount = Amount * TaxRate => FD: Amount, TaxRate → TaxAmount) and one of them should be deleted or guaranteed that they will be agreed.
Obviously, you could say, but if the system you are building is intended for an audit company, then you may not have a functional dependency - they may audit someone who uses manual calculations or has faulty software or should be able to record incomplete data, and the calculation may be erroneous initially, and as an audit company you should record the fact how this happened.
Thus, the semantics (predicates) that are defined by the requirements will affect if any of the normal forms is violated - affecting the functional dependencies (in other words, the correct establishment of functional dependencies is a very important part of modeling when you are striving for a normalized database )