When working on my side projects or in a production ERP, the structure called a "product tree" always comes up. Essentially, it's a hierarchical structure that shows which components a product consists of, and how those components are further divided into sub-components. In a database, it's usually modeled with a self-referencing table or a separate relationship table.
This structure plays a key role, especially in critical processes like production planning, cost calculation, and inventory management. However, when complex queries and performance issues arise, denormalization immediately comes to mind. But is it always the right solution, especially in our small personal projects?
The Product Tree Complexity and My First Experiences
I first experienced the true complexity of a product tree when I was working in the ERP of a large manufacturing company. Imagine an engine consisting of hundreds of parts, with each part having dozens of sub-parts. When one part changed, the entire tree needed to be affected, and this had to be reflected in the costs.
In such situations, software architecture often reflects organizational flow more than just software. When designing the database model, you have to consider not only how data will be stored but also the actual workflows on the production line. A simple raw material change can directly affect the final product's price and delivery time.
