# Postgresql 庫存 與 BOM 應用
庫存管理是常見的應用, 剛好ithelp有人問了這問題,就以此為例. [按此前往](https://ithelp.ithome.com.tw/questions/10215843)
## Table 規劃

-- create schema 
create schema s240608;

-- 首先建立產品table
create table s240608.products (
  id int generated always as identity primary key
, prod_name text
);

insert into s240608.products (prod_name)
values ('🍰'), ('🥧'), ('🥞');

select *
  from s240608.products;
 id | prod_name
----+-----------
  1 | 🍰
  2 | 🥧
  3 | 🥞
(3 rows)

-- 物料表
create table s240608.materials (
  id int generated always as identity primary key
, mtrl_name text
);

insert into s240608.materials (mtrl_name)
values ('🌾'), ('🍎'), ('🥚'), ('🥛'); 

select *
  from s240608.materials;

 id | mtrl_name
----+-----------
  1 | 🌾
  2 | 🍎
  3 | 🥚
  4 | 🥛
(4 rows)

-- Bill of Material
create table s240608.bom (
  pid int references s240608.products (id)
, mid int references s240608.materials (id)
, qty int
, primary key (pid, mid) 
);

create index on s240608.bom (pid);
create index on s240608.bom (mid);  
  
insert into s240608.bom values
(1, 1, 30), (1, 3, 1), (1, 4, 2),
(2, 1, 50), (2, 2, 1), (2, 4, 1),
(3, 1, 20), (3, 3, 2), (3, 4, 2);

select p.prod_name
     , b.qty
     , m.mtrl_name
  from s240608.bom b
  join s240608.products p
    on b.pid = p.id
  join s240608.materials m
    on b.mid = m.id
 order by b.pid, b.mid;

 prod_name | qty | mtrl_name
-----------+-----+-----------
 🍰        |  30 | 🌾
 🍰        |   1 | 🥚
 🍰        |   2 | 🥛
 🥧        |  50 | 🌾
 🥧        |   1 | 🍎
 🥧        |   1 | 🥛
 🥞        |  20 | 🌾
 🥞        |   2 | 🥚
 🥞        |   2 | 🥛
(9 rows)

-- 運用 JSON 展現另一種可讀性較高的方式
select p.prod_name
     , json_agg(json_build_object(m.mtrl_name, b.qty))
  from s240608.bom b
  join s240608.products p
    on b.pid = p.id
  join s240608.materials m
    on b.mid = m.id
 group by p.prod_name; 

 prod_name |               json_agg
-----------+---------------------------------------
 🍰        | [{"🌾" : 30}, {"🥚" : 1}, {"🥛" : 2}]
 🥧        | [{"🌾" : 50}, {"🍎" : 1}, {"🥛" : 1}]
 🥞        | [{"🌾" : 20}, {"🥚" : 2}, {"🥛" : 2}]
(3 rows)

-- 庫存表
create table s240608.inventory (
  id int references s240608.materials (id) primary key
, qty int
);

insert into s240608.inventory values
(1, 222), (2, 10), (3, 99), (4, 30);

select m.mtrl_name
     , i.qty
  from s240608.inventory i
  join s240608.materials m
    on i.id = m.id
 order by i.id;

 mtrl_name | qty
-----------+-----
 🌾        | 222
 🍎        |  10
 🥚        |  99
 🥛        |  30
(4 rows)

可以觀察到使用了 FK. BOM 的 PK 使用組合方式. 庫存表的 PK 也是 FK.
## 生產產品消耗庫存

-- make 1 🍰

select p.prod_name
     , b.qty
     , m.mtrl_name
  from s240608.bom b
  join s240608.products p
    on b.pid = p.id
  join s240608.materials m
    on b.mid = m.id
 where p.prod_name = '🍰';

 prod_name | qty | mtrl_name
-----------+-----+-----------
 🍰        |  30 | 🌾
 🍰        |   1 | 🥚
 🍰        |   2 | 🥛
(3 rows)

with consume as (
  select m.id 
       , b.qty as cqty
    from s240608.bom b
    join s240608.products p
      on b.pid = p.id
    join s240608.materials m
      on b.mid = m.id
   where p.prod_name = '🍰'
)
update s240608.inventory as i
   set qty = qty - c.cqty
  from consume as c
 where i.id = c.id; 

-- current
select m.mtrl_name
     , i.qty
  from s240608.inventory i
  join s240608.materials m
    on i.id = m.id
 order by i.id;

 mtrl_name | qty
-----------+-----
 🌾        | 192
 🍎        |  10
 🥚        |  98
 🥛        |  28
(4 rows)

---
-- make 3 🥞

select p.prod_name
     , b.qty * 3 as qty
     , m.mtrl_name
  from s240608.bom b
  join s240608.products p
    on b.pid = p.id
  join s240608.materials m
    on b.mid = m.id
 where p.prod_name = '🥞';

 prod_name | qty | mtrl_name
-----------+-----+-----------
 🥞        |  60 | 🌾
 🥞        |   6 | 🥚
 🥞        |   6 | 🥛
(3 rows)

with consume as (
  select m.id 
       , b.qty * 3 as cqty
    from s240608.bom b
    join s240608.products p
      on b.pid = p.id
    join s240608.materials m
      on b.mid = m.id
   where p.prod_name = '🥞'
)
update s240608.inventory as i
   set qty = qty - c.cqty
  from consume as c
 where i.id = c.id; 

-- current
select m.mtrl_name
     , i.qty
  from s240608.inventory i
  join s240608.materials m
    on i.id = m.id
 order by i.id;

 mtrl_name | qty
-----------+-----
 🌾        | 132
 🍎        |  10
 🥚        |  92
 🥛        |  22
(4 rows)

## 為何不用 JSON 存 BOM
雖然有展示了使用 JSON 格式來顯示 BOM, 但是不是存成 JSON.
因為存成JSON要使用時是要展開的,展開成原本的BOM再做 JOIN 並做後續處理.
當然更不會是去解開JSON,再用迴圈一筆筆尋找然後更新.
## 結論
雖然是常見的應用,透過正規化的table設計,可以讓我們使用SQL就能扣除庫存管理,並避免無謂操作.