I am trying to work out the best schema structure to represent a BoM in Postgres. Assuming a part can have multiple of the same child part, I could add a quantity column, but those parts may also have multiple children.
If I wanted to know the total usage of each part does postgres have a way of using the quantity column in a hierarchical query?
BOM means Bill Of Material.
As far as I understand your question, then yes, you can include the quantity when using a hierarchical BOM. The way I understand your question is, that if one BOM entry has an amount of e.g. 10, then the amount for its children needs to be multiplied with 10 (because you have 10 times that "child" item).
With the following table and sample data:
create table bom_entry
(
entry_id integer primary key,
product text, -- should be a foreign key to the product table
amount integer not null,
parent_id integer references bom_entry
);
insert into bom_entry
values
(1, 'Box', 1, null),
(2, 'Screw', 10, 1),
(3, 'Nut', 2, 2),
(4, 'Shim', 2, 2),
(5, 'Lock', 2, 1),
(6, 'Key', 2, 5);
So our box needs 10 screws and every screw needs 2 nuts and 2 shims, so we need a total of 20 nuts and 20 shims. We also have two locks and each lock has two keys, so we have a total of 4 keys.
You can use a recursive CTE to go through the tree and calculate the amount for each item.
with recursive bom as (
select *, amount as amt, 1 as level
from bom_entry
where parent_id is null
union all
select c.*, p.amt * c.amount as amt, p.level + 1
from bom_entry c
join bom p on c.parent_id = p.entry_id
)
select rpad(' ', (level - 1)*2, ' ')||product as product, amount as entry_amount, amt as total_amount
from bom
order by entry_id;
The rpad/level is used to do the indention to visualize the hierarchy. The above query returns the following:
product | entry_amount | total_amount
---------+--------------+-------------
Box | 1 | 1
Screw | 10 | 10
Nut | 2 | 20
Shim | 2 | 20
Lock | 2 | 2
Key | 2 | 4