The Anatomy of ERP Bill of Materials Denormalization: A Guide for Performance and Scalability
In a manufacturing ERP, after hours of analysis, we finally found the root cause of a performance bottleneck in the Bill of Materials. The situation was, in fact, due to complications arising from denormalization, a fairly common problem. In this post, I will delve into the anatomy of Bill of Materials denormalization in ERP systems; I will discuss the concrete problems I encountered, their causes, and solutions in detail. Drawing from my experiences, I will explain ways to make this complex structure more performant and scalable.
Such denormalizations are initially done to reduce data redundancy and improve query performance. However, over time and as data volume increases, these strategies can lead to unexpected performance issues. Especially hierarchical and frequently queried data structures like the Bill of Materials are among the first to feel the negative effects of denormalization. In this post, by sharing my own cases and the lessons learned from them, I aim to guide technology professionals facing similar situations.
Bill of Materials Structures and Fundamental Problems
A Bill of Materials (BOM) is a hierarchical structure that defines the components, sub-components, and their quantities for a main product. Traditionally, various methods are used to represent these structures in a database: adjacency list, nested set, materialized path, and closure table. Each has its own advantages and disadvantages. However, a common situation in ERP systems is the "denormalization" of these structures to meet performance requirements.
