Hello! My name is Andrey Semenov, and I am a senior analyst at Sportmaster. In this post, I want to raise the issue of denormalization of ERP database systems. We will examine the general conditions, as well as a specific example — let’s say it will be a fine monopolistic tavern for pirates and sailors. In which pirates and sailors must be served differently, as their perceptions of beauty and consumer patterns significantly differ.
How can we ensure everyone is satisfied? How to avoid going crazy while designing and maintaining such a system? What to do if not only the usual pirates and sailors start coming to the tavern?
Everything will be below. But let's go step by step.
1. Limitations and Assumptions
All stated applies only to relational databases. Well-documented, including online, consequences of denormalization in the form of modification, deletion, and insertion anomalies are not considered. Cases where denormalization is commonplace, with classic examples such as series and passport number, date and time, etc., are outside the scope of this publication.
The post uses intuitively understandable and practically applicable definitions of normal forms, without references to mathematical terms. In the manner they can be applied to the examination of real business processes (BP) and industrial software design.
There is an opinion that designing data warehouses, reporting tools, and integration agreements (in which tabular representation of information is used) differs from designing ERP database systems in that the ease of consumption and the conscious denormalization needed to achieve it may take precedence over data integrity protection. I share this opinion, and what is described below pertains exclusively to master data and transactional data models of ERP systems.
The explanation of normal forms is presented using an example that is easily understandable at a practical level for most readers. However, as a clear illustration in points 4-5, a deliberately "fabricated" problem has been used. If this isn't done and a standard example is taken, such as the order storage model from point 2, there can be a situation where the reader's focus shifts from the suggested breakdown of the process model to their personal experiences and perceptions of how processes and data storage models in information systems should be structured. In other words, take two qualified IT analysts, one serving logistics for passenger transport and the other for logistics transporting machinery for microchip production. Ask them, without previously discussing the automatable business processes, to create a data model for storing information about a railway journey.
There is a non-zero probability that in the proposed models, you'll find not only noticeably different sets of attributes but also varying sets of entities, as each analyst will rely on the processes and tasks they are accustomed to. It is impossible to say which model is "correct" in such situations because there is no evaluation criterion.
2. Normal Forms
The First Normal Form of a Database requires all attributes to be atomic.
In particular, if an object A has non-key attributes a and b, such that c=f(a,b) and in the table describing object A you store the value of attribute c, then the First Normal Form is violated. For example, if an order specification indicates a quantity where the units of measurement depend on the type of product: in one case it could be pieces, in another liters, and in a third packs consisting of pieces (in the above model Good_count_WR), then the atomicity of the attributes is violated in the database. In this case, to determine what the table structure for the order specification should be, a targeted description of the process in the information system is needed, and since processes may vary, there can be many "correct" versions.
The Second Normal Form of a Database requires adherence to the first normal form and a separate table for each entity related to the process within the information system. If there are dependencies in one table with s=f1(a) and d=f2(b) and no dependency with s=f3(b), then the second normal form is violated in the table. In the example above, the 'Order' table does not show a dependency between the order and the address. Change the street name or city, and it will have no impact on the essential attributes of the order.
Third normal form of the database requires adherence to the second normal form and the absence of functional dependencies between attributes of different entities. This rule can be formulated as: 'everything that can be calculated should be calculated.' In other words, if there are two objects A and B. In the table storing attributes of object A, attribute C is revealed, and object B has an attribute b such that there exists c=f4(b), then the third normal form is violated. In the example below, the attribute 'Total count' (Total_count_WR) in the order record clearly claims a violation of the third normal form.
3. My approach to applying normalization
1. Only the targeted automatable business process can provide the analyst with criteria for identifying entities and attributes when creating a data storage model. Creating a process model is a mandatory condition for developing a normal data model.
2. Achieving third normal form in strict terms may be impractical in real ERP system development practices when part or all of the following conditions are met:
- automatable processes are rarely subject to changes,
- the timelines for research and development are tight,
- requirements for data integrity are conditionally low (potential errors in industrial software do not lead to loss of money or clients for the software customer)
- ".
Under the described conditions, the costs associated with identifying and describing the lifecycle of certain objects and their attributes may not be justified in terms of economic efficiency.
3. Any consequences of denormalizing the data model in an already created information system can be mitigated by thorough preliminary code research and testing.
4. Denormalization is a way to shift labor costs from the data source research and business process design phase to the development phase, from the implementation period to the system development period.
5. It is advisable to strive for the third normal form of the database if:
- The direction of change in automated business processes is difficult to predict.
- There is a weakly permeable division of labor within the implementation and/or development team.
- The systems within the integration contour are evolving according to their own plans.
- Data inconsistency can lead to the loss of customers or money for the company.
6. The design of the data model should be carried out by an analyst only in relation to the models of the target business process and the process in the information system. If a developer is involved in designing the data model, they will need to immerse themselves in the subject area to the extent that they can understand the difference between attribute values—an essential condition for identifying atomic attributes. This means they are taking on functions that do not belong to them.
4 Task for illustration
Suppose you have a small robotic tavern in a port. Your market segment: sailors and pirates who come to port and need rest. You sell sailors thyme tea, while pirates buy rum and bone combs for grooming their beards. The service in the tavern is provided by a robot hostess and a robot bartender. Thanks to high quality and low prices, you have driven out all competitors, so everyone coming off a ship visits your tavern, which is the only one in the port.
The information systems complex of the tavern consists of the following software:
- Early customer warning system that recognizes their category based on distinctive features.
- Management system for robot hostesses and robot bartenders.
- Warehouse and delivery management system to the point of sale.
- Supplier relationship management system (SRM).
Process:
The early warning system recognizes people disembarking from ships. If a person is clean-shaven, they are identified as a sailor; if they have a beard, they are identified as a pirate.
Upon entering the tavern, a guest hears a greeting from the robot hostess according to their category, for example: 'Ho-ho-ho, esteemed pirate, please proceed to table number…'
The guest approaches the designated table where the robot bartender has already prepared items according to the category. The robot bartender transmits information to the warehouse system that the next batch of delivery needs to be increased, and the warehouse information system forms a purchasing request in the procurement system based on the stock levels.
Let the early warning system be developed by your internal IT, and the program for managing the bar robots be created by an external contractor specifically for your business. Meanwhile, the systems for managing the warehouse and supplier relationships are customized out-of-the-box solutions from the market.
5. Examples of denormalization and its impact on software development
When designing the business process, the surveyed domain experts unanimously stated that around the world, pirates drink rum and comb their beards with bone combs, while sailors drink thyme tea and are always clean-shaven.
A directory of client types appears with two values: 1 - pirates, 2 - sailors, common across the entire information framework of the company.
The client alert system immediately saves the result of image processing as an ID of the recognized client and their type: sailor or pirate.
ID of the Recognized Object
Client Category
100500
Pirate
100501
Pirate
100502
Sailor
Once again, let's note that
1. Our sailors are actually clean-shaven people
2. Our pirates are actually bearded people
What issues need to be addressed in this case so that our structure aims towards the third normal form:
- violation of attribute atomicity - Client Category
- mixing the analyzed fact and conclusion in one table
- a fixed functional dependency between attributes of different entities.
In normalized form, we would have received two tables:
- the recognition result in the form of a set of established features,
ID of the Recognized Object
Facial hair
100500
Yes
100501
Yes
100502
No
- the result of identifying the client type as an application of the logic laid down in the information system for interpreting established features
ID of the Recognized Object
Identification ID
Client Category
100500
100001
Pirate
100501
100002
Pirate
100502
100003
Sailor
How can a normalized data storage organization facilitate the development of an IS complex? Let's assume that suddenly you have new clients. Let’s say these are Japanese pirates who may be clean-shaven but walk around with a parrot on their shoulder, and eco-pirates, easily identifiable by Greta's blue profile on their left chest.
Eco-pirates, of course, cannot use bone combs and demand an alternative made from recycled marine plastic.
You need to revise the algorithms of the programs according to the new inputs. If normalization rules had been followed, you would only need to adjust the inputs for some branches of the processes in certain systems and create new branches only for those cases and in those IS where the presence of facial hair matters. However, since the rules were not followed, you will have to analyze all the code throughout the scope where client type reference values are used and clearly determine where the algorithm must consider the client's professional activity in one case and physical characteristics in another.
In the form that strives for normalization, we would have two tables with operational data and two reference directories:
- the recognition result in the form of a set of established features,
ID of the Recognized Object
Greta on the left chest
Bird on the shoulder
Facial hair
100510
1
1
1
100511
0
0
1
100512
1
0
- result of determining the client type (let's say this is a user representation that displays descriptions from reference directories)
Does the detected denormalization mean that the systems cannot be adapted to new conditions? Of course not. If we imagine that all IS were created by a single team with zero staff turnover, the developments are well documented, and information is transmitted within the team without losses, then the required changes can be made with negligibly small effort costs. But if we return to the initial conditions of the task, just printing the minutes of joint discussions will wear out 1.5 keyboards and another 0.5 for formatting procurement procedures.
In the above example, all three normal forms are violated; let’s try to violate them separately.
Violation of the first normal form:
Suppose goods are delivered to your warehouse from suppliers by self-pickup using a 1.5-ton Gazelle owned by your tavern. The size of your orders is so small compared to the suppliers' turnovers that they are always executed one-to-one without waiting for production. Do you need separate tables for vehicles, types of vehicles, and should you separate planned and actual orders sent to suppliers?
Just imagine how many 'extra' connections your programmers will have to write if the program development uses the model below.
Suppose we decided that the proposed structure is overly complicated; in our case, separating planned and actual in the order record is excessive information, and the created order specification is overwritten based on the results of receiving the delivered goods, with rare sorting discrepancies and receipt of poor quality goods being handled outside of the information system.
One day you see that the entire tavern hall is filled with angry and unkempt pirates. What happened?
It turned out that along with the growth of your business, consumption also increased. At some point, a management decision was made that if the Gazelle was overloaded in volume and/or weight, which happened very rarely, the supplier prioritized loading in favor of beverages.
Under-delivered goods ended up in the next order and were sent in a new trip; having a minimum stock in the tavern allowed the overlooked cases to go unnoticed.
In the port, the last competitor closed down, and the overlooked case of Gazelle overload, which was sidestepped due to prioritization based on the assumption of sufficient minimum stock and periodic underloading of the vehicle, became common practice. The created system will operate perfectly according to the algorithms laid out within it and will lack any capability to track systematic failure to fulfill planned orders. Only a damaged reputation and dissatisfied customers will be able to detect the problem.
A careful reader may have noticed that the ordered quantity in the order specification (T_ORDER_SPEC) in section 2 and in section 5 may or may not meet the requirement of the first normal form. It all depends on whether different units of measurement can fall into the same field based on the selected range of products.
Violation of the second normal form:
As your needs grow, you acquire a couple of transport vehicles of different sizes. In the context described above, creating a vehicle directory was deemed redundant, resulting in all data handling algorithms serving delivery and warehouse needs perceiving the movement of goods from the supplier to the warehouse as a trip exclusively of a 1.5-ton Gazelle. Therefore, along with the purchase of new vehicles, you still create a vehicle directory, but during further development, you will have to analyze all the code that refers to the movement of goods to determine whether specific references to the characteristics of that very vehicle, from which the business started, are implied in each specific place.
Violation of the third normal form:
At some point, you begin to create a loyalty program, and a record of a regular customer appears. Why, for example, spend time creating tangible representations that store aggregated sales data per individual customer for reporting and transferring to analytical systems, when at the initial stage of the loyalty program, all that interests the customer can be placed on the record of the customer themselves? And indeed, at first glance, there seems to be no point. But every time your business connects new sales channels, there should be someone among your analysts who remembers that there exists such an aggregation attribute.
When designing each new process, for example, online sales, sales through distributors connected to the common loyalty system, someone must keep in mind that all new processes must ensure data integrity at the code level. For an industrial database with a thousand tables, this seems like an impractical task.
An experienced developer certainly knows how to address all the aforementioned issues, but in my view, the role of an experienced analyst is to prevent them from occurring in the first place.
I want to express my gratitude for the valuable feedback during the preparation of the publication to lead developer Yevgeny Yaryuhin.
Literature
Connolly, Thomas, Begg, Carolyn. Databases. Design, Implementation, and Maintenance. Theory and Practice
Source: habr.com
