Identifying the Bill of Materials (BOM) Structure in Databases

The bill of materials (BOM) design pattern is deceptively simple, yet incredibly powerful. Historically, it’s been employed to model product structures, but the pattern can be used to do much more than simply define a hierarchy. This article will introduce three very different examples to help you to recognize the pattern in your own projects.

What Is a Bill of Materials, or BOM?

A bill of materials has its roots in manufacturing. It is a list of the raw materials, sub-assemblies, intermediate assemblies, sub-components, parts, and the quantities of each needed to manufacture an end product. You can look at it as a hierarchical decomposition of a product. Other terms for the same thing are product structure, bill of material, and associated list.

To illustrate a BOM, look at the conceptual model below. It starts with the top-level product, Car. Broadly speaking, a Car has an Engine and a Body. In this example, there are different types of engines: V6 and V8. There are different types of bodies: 3-door, 5-door, and estate (also known as a wagon or station wagon). The process of decomposition can go down to the very last nut and bolt – or even dab of glue – but you get the picture.

At the simplest level, you’re joining two parts together in the form of a hierarchy – a parent part to a child part – from the top of the hierarchy right down to the bottom. The most basic manufacturing BOM model looks like this:

This is the classic BOM structure, where a single [parent] table has two relationships with a [child] junction table.

Here’s the simple product hierarchy from the Car example:

BOMs in manufacturing tend to have the same kind of major properties:

• Assemblies, sub-assemblies, and individual components can be reused. For example, the same kind of bolt can be used in different types of assembly.
• There often needs to be a hierarchy-specific quantity. For example, it’s important to know that one assembly needs 10 bolts, but another assembly might need 15 bolts of the same specification.

Once you define an assembly, its structure is automatically imported into any other assemblies that make use of it. So if that assembly was to change, then all other BOM’s that use it will automatically be updated. BOMs that describe sub-assemblies like this are referred to as modular BOMs.

For manufacturers, a BOM is a critical piece of product information, a record that lists everything needed to manufacture a product. Advanced modelling techniques are required to handle configurable products, component variations, or substitute components. Changing a small part of a product can have multiple impacts on other product BOMs. Without taking these into consideration, BOM management can become quite unmanageable.

But this specialist area is beyond the scope of this article. Instead, we’ll focus on examples of where BOM structures might occur in database design. Once you can recognize a BOM, you’ll be able to make use of this powerful design pattern.

We’ll start with a common example: the many-to-many relationship between flights and airports.

What Does the Bill of Materials Pattern Have to Do with Flights?

Here’s the conceptual model:

Imagine yourself at any airport in the world. From there, you will be able to see planes taking off for other destinations. You will also see planes landing from other destinations. So there is a many-to-many relationship between departure and arrival airports.

Typically, we resolve this many-to-many relationship using a junction table:

The Flight class will have its own attributes, including flightNumber, scheduledDepartureTime, and scheduledArrivalTime.

Looking back at our model, we may spot a minor issue. We know that there is no such thing as a DepartureAirport or an ArrivalAirport. They’re both just airports from which flights depart and to which flights arrive.

So we merge DepartureAirport and ArrivalAirport into a single airport table like this:

Again this follows the classic BOM structure, where a single [parent] table has two relationships with a [child] junction table.

Conceptually though, there is a big difference between this and a manufacturing BOM. This BOM has no true hierarchical structure. It’s completely flat. Why do I say this?

It’s best described by way of an example.

First, let’s consider some sample data for this BOM:

Now we’ll work through an example. Imagine you need to fly from Manchester to Chennai. There are no direct flights. But you can fly from Manchester to Dubai, the first leg of your journey. You can then take another flight from Dubai to Chennai, the second leg of your journey. While the two legs form your itinerary, in no way is the second leg some sort of sub-component of the first leg! Therefore, this structure is flat.

But note the 1:1 correspondence of data between the parts and flights examples: Car → Manchester; Engine → Dubai; Chennai → V6.

In the car example, the parts form a tightly-coupled hierarchy. In the airport example, flights can be traversed to form more loosely-coupled connections between flights. For a passenger flying from Manchester to Chennai, an itinerary needs to be created. This is the result of a query, which takes into account what constitutes a connection – e.g. the minimum and maximum time between flights; whether the same airline must be used or if different airlines are permitted.

Next, let’s look at how BOM can be used to describe relationships in data modeling.

Relationships in the BOM Structure

By this I mean relationships between people, between organizations, and between organizations and people. These are real-world relationships, such as a someone being an employee of a company or a member of a team, or of a company owning another company. The conceptual model looks like this:

If you were to map this to directly to a physical model, you would have junction tables for each of the many-to-many relationships. This can get a bit cluttered, and it doesn’t help with running queries – for example, finding all the relationships a Person has.

So it’s probably better to recognize that Person and Organization are different types of Party. This allows us to simplify the three many-to-many relationships into a single one:

If your requirements are simple this may be sufficient. But in the real world, things don’t tend to be that simple. For example, an employee may leave a company to go travelling around the world for a time. When he returns from his travels, he looks for work and is re-hired by the company he left. (It happens!) The employee, therefore, has two separate instances of a relationship with this employer, each with different effective dates, and possibly with a different employee ID.

So the relationship itself requires attributes. This means another entity, Relationship, is required to contain them:

Again this follows the classic BOM structure, where a single [parent] table has two relationships with a [child] junction table.

By convention, in this model the 1st interactor tends to be the superior Party in the Relationship such as the employer rather than the employee, or the team leader rather than the team member.

This party-relationship BOM pattern can be used to list all the employees (2nd interactor) in an organization (1st interactor) at the contractual level if you will. This is a flat, single level hierarchy. It can also be used simultaneously to define the entire management reporting structure (or hierarchy) at the same organization, which can have any number of levels. For instance: an employee may work under one contract for a number of years but may find himself working for different managers over that period (1^st interactor = responsible for; 2^nd interactor = reports to). He may even work concurrently for more than one manager.

Here’s what the data might look like (with their respective roles in parentheses):

Get to Know the BOM

Whilst the bill of materials structure has its roots in manufacturing, it can be used for different purposes, which can range from something strictly hierarchical and tightly-coupled to something fairly flat and more loosely-coupled.

My hope is that these examples will help you to recognize the BOM pattern if it exists in your projects. Once you recognize the pattern, you will understand how it should be implemented. You don’t have to reinvent the wheel each time – you just need to tailor it to your specific requirements.