在庫管理システムの設計

前回の記事に引き続き、Spring Bootを使用して在庫管理システムを構築する。
*下記で紹介した書籍を元に実装する
*今後、実装内容を一部メモとして掲載していく予定
https://qiita.com/ktkt11122334/items/20359679cd4abb2303b7

そのために今回は、データベースの論理/物理設計を行った。
その際にメモ書きした要件/ER図/DDLを本記事にメモ程度に残す。
*メモ書きの転記なので、誤字脱字、実際に使用する値と異なるもの、インデックス未定義などがある
*ER図はリレーションを視覚化しただけであり、パラメータは割り振っていない
*インデックスの定義は、使用するSQLが決定した時点で定義する予定
*実装する過程で、設計を変更するものもある

**設計ミスがあると思いますので、参考程度までに

ER図

要件

在庫管理を行うシステムを構築する。

・・・・マスタ系・・・・
—商品マスタ
業務上で扱う商品データを登録し、アプリケーション内で業務内容に関連付けた商品情報を保存する。
　　　商品idは、アプリケーション上で商品を一意に識別するidである。
　　　商品コードは、実務上で商品を一意に識別するコードである。
　　　各商品には、商品単価の単価を保存する
　　　商品単価と商品に紐づく消費税率で商品金額が定まる( price * tax_rate )
　　　商品マスタのデータは更新によって都度、トランザクションデータとしてレコードを挿入し、
　　　商品idと最終更新日時の組み合わせで新しく更新された最新情報を管理及び使用する

—税率マスタ
　税率情報を保持するテーブル
　　商品マスタとの組み合わせで税率込みの商品価格を算出できるようにする
　　消費税率の変更日付を管理する
　　現在日付が税率変更日付と同日のなった場合、商品にかかる税率を切り替える

—顧客マスタ
　顧客が商品を購入すると、購入時に登録される顧客情報を顧客マスターに登録する
　　顧客情報は、顧客名、電話番号、住所情報、メールアドレスを管理する
　　顧客情報は、顧客名、電話番号で顧客を一意に判別する
　　顧客情報が重複した場合は、同一顧客として扱う

・・・・トランザクション系・・・・
　—注文
　　顧客が注文を行うと注文情報が登録される。
　　注文情報入力画面で新規の顧客情報が入力されると新規顧客情報が顧客テーブルに保存される
　　また、注文された商品の税額を含む金額合計(order_price)が保存される。
　　注文情報の注文ステータス(order_status)は、注文情報作成と注文出荷済みがあり、
　　新規に注文が作成された場合、注文情報作成となり
　　登録された注文内容の商品が顧客に出荷されると注文出荷済みになる。
　　出荷完了した注文には、出荷日が登録される

　—注文明細
　　顧客が注文した商品情報とその個数が保存される
　　受注明細の商品情報は、注文情報が作成された当時の商品情報が記載される

　—在庫情報
　　在庫管理の単位は、商品マスター毎に管理される。
　　在庫情報には、下記の情報がある
　　　実在庫数：実際に在庫が存在している個数
　　　出荷予定数：注文ステータスが出荷完了以前の注文明細の出荷予定商品個数
　　　基準在庫数：最低限在庫として保有していなければならない個数で、実在庫数が基準在庫数を下回ると警告される
　　新規で注文情報が作成されると、出荷予定数に注文された出荷予定の商品個数の総和が記録される
　　出荷完了すると、出荷済みとなった受注の商品個数が出荷予定数から減算され、
　　実在庫数からも減算される

　—在庫消費
　　出荷された注文単位で消費した在庫の内容を管理するテーブル
　　注文が出荷される毎に出荷された注文番号とその注文の顧客情報、
　　在庫から商品が消費された日付とその商品個数が記録される

　　このテーブルは在庫分析にも使用する。
　　ーー
　　　1. 在庫消費日付毎、商品毎の商品の消費量を表示する
　　　2. 在庫消費した顧客毎、消費日付毎、商品毎の顧客の消費量を表示する
　　これらは、毎日の夜間バッチでその日消費された在庫情報を在庫消費テーブルから集計され、
　　各集計テーブルに挿入される。
　　集計結果表示画面より、その結果を確認することができる。
　　アプリケーション管理者は、この集計結果をもとに今後、在庫補充する商品の個数を考察する

DDL

＊＊＊＊＊＊＊＊＊＊メニュー画面の表示に使用するDDL(上記設計では未定義の項目)＊＊＊＊＊＊＊＊＊＊
CREATE TABLE IF NOT EXISTS menu ( 
  menu_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  display_name VARCHAR(255) NOT NULL, 
  path VARCHAR(255) NOT NULL, 
  display_order INT UNSIGNED NOT NULL, 
  disable_flg BOOLEAN DEFAULT 0 
);
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊

[product_master]
CREATE TABLE IF NOT EXISTS product_master ( 
  product_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  product_code VARCHAR(6) NOT NULL, 
  jancode VARCHAR(11) NOT NULL, 
  disable_flg BOOLEAN DEFAULT 0, 
  create_ts TIMESTAMP(5) NOT NULL, 
  last_modified_ts TIMESTAMP(5) NOT NULL 
);

[tax]
CREATE TABLE IF NOT EXISTS tax ( 
  tax_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  tax_rate TINYINT NOT NULL, 
  from_change_date DATE NOT NULL, 
  create_ts TIMESTAMP(5) NOT NULL, 
  last_modified_ts TIMESTAMP(5) NOT NULL 
);

[product_master_history]
CREATE TABLE IF NOT EXISTS product_master_history ( 
  product_id BIGINT UNSIGNED , 
  last_modified_ts TIMESTAMP(5), 
  product_name VARCHAR(255), 
  price INT NOT NULL, 
  tax_id BIGINT UNSIGNED NOT NULL, 
  create_ts TIMESTAMP(5) NOT NULL, 

  PRIMARY KEY(product_id, last_modified_ts), 
  CONSTRAINT fk_product_id FOREIGN KEY(product_id) REFERENCES product_master(product_id) ON UPDATE CASCADE ON DELETE CASCADE, 
  CONSTRAINT fk_tax_id FOREIGN KEY(tax_id) REFERENCES tax(tax_id) ON UPDATE CASCADE 
);

[customer]
CREATE TABLE IF NOT EXISTS customer ( 
  customer_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  postal_code VARCHAR(20) NULL, 
  first_name VARCHAR(100) NOT NULL, 
  last_name VARCHAR(100) NULL, 
  tell_number VARCHAR(20) NOT NULL, 
  first_address VARCHAR(255) NOT NULL, 
  last_address VARCHAR(255) NOT NULL, 
  mail_address VARCHAR(255) NOT NULL, 
  create_ts TIMESTAMP(5) NOT NULL, 
  last_modifid_ts TIMESTAMP(5) NOT NULL 
);

[order_header]
CREATE TABLE IF NOT EXISTS order_header ( 
  order_id BIGINT(11) ZEROFILL NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  order_price INT NOT NULL, 
  customer_id BIGINT UNSIGNED NOT NULL, 
  order_status TINYINT NOT NULL, 
  shipping_date DATE, 
  create_ts TIMESTAMP(5) NOT NULL, 
  last_modifid_ts TIMESTAMP(5) NOT NULL, 

  CONSTRAINT fk_customer_id FOREIGN KEY(customer_id) REFERENCES customer(customer_id) ON UPDATE CASCADE 
);

[order_detail]
CREATE TABLE IF NOT EXISTS order_detail ( 
  order_id BIGINT(11) ZEROFILL NOT NULL, 
  order_detail_id BIGINT UNSIGNED NOT NULL, 
  parchase_number INT NOT NULL, 
  product_id BIGINT UNSIGNED NOT NULL, 
  product_last_modified_ts TIMESTAMP(5), 
  create_ts TIMESTAMP(5) NOT NULL, 
  last_modifid_ts TIMESTAMP(5) NOT NULL, 

  PRIMARY KEY(order_id, order_detail_id), 
  CONSTRAINT fk_purchased_product FOREIGN KEY(product_id, product_last_modified_ts) REFERENCES product_master_history(product_id, last_modified_ts) ON UPDATE CASCADE 
);

[stock]
CREATE TABLE IF NOT EXISTS stock ( 
  stock_id BIGINT UNSIGNED  NOT NULL PRIMARY KEY,
  product_id BIGINT UNSIGNED UNSIGNED NOT NULL, 
  actual_stock_number INT UNSIGNED DEFAULT 0,  
  future_shipped_stock_number INT UNSIGNED DEFAULT 0,  
  standard_stock_number INT UNSIGNED DEFAULT 0,  
  create_ts TIMESTAMP(5) NOT NULL, 
  last_modifid_ts TIMESTAMP(5) NOT NULL, 

  CONSTRAINT fk_stock_product FOREIGN KEY(product_id) REFERENCES product_master(product_id) ON UPDATE CASCADE 
);

[stock_consumer]
CREATE TABLE IF NOT EXISTS stock_consumer ( 
  stock_consumer_id BIGINT UNSIGNED  NOT NULL PRIMARY KEY,
  stock_id BIGINT UNSIGNED  NOT NULL,
  consumer_product_number INT UNSIGNED DEFAULT 0,
  consumer_date DATE NOT NULL, 
  order_id BIGINT(11) ZEROFILL NOT NULL, 
  order_detail_id BIGINT UNSIGNED NOT NULL, 
  customer_id BIGINT UNSIGNED NOT NULL, 
  product_id BIGINT UNSIGNED NOT NULL,
  create_ts TIMESTAMP(5) NOT NULL, 
  last_modifid_ts TIMESTAMP(5) NOT NULL, 

  CONSTRAINT fk_consumed_stock FOREIGN KEY(stock_id) REFERENCES stock(stock_id) ON UPDATE CASCADE, 
  CONSTRAINT fk_stock_order FOREIGN KEY(order_id, order_detail_id) REFERENCES order_detail(order_id, order_detail_id) ON UPDATE CASCADE, 
  CONSTRAINT fk_stock_customer_id FOREIGN KEY(customer_id) REFERENCES customer(customer_id) ON UPDATE CASCADE, 
  CONSTRAINT fk_consumed_product FOREIGN KEY(product_id) REFERENCES product_master(product_id) ON UPDATE CASCADE
);

[consumer_product_number]
CREATE TABLE IF NOT EXISTS consumer_product_number ( 
  product_id BIGINT UNSIGNED NOT NULL, 
  consumer_date DATE NOT NULL, 
  consumer_number INT UNSIGNED NOT NULL DEFAULT 0, 
  create_ts TIMESTAMP(5) NOT NULL, 
  last_modifid_ts TIMESTAMP(5) NOT NULL, 

  PRIMARY KEY(product_id, consumer_date), 
  CONSTRAINT fk_aggregation_product FOREIGN KEY(product_id) REFERENCES product_master(product_id) ON UPDATE CASCADE 
);

[consumer_product_number_for_customer]
CREATE TABLE IF NOT EXISTS consumer_product_number_for_customer ( 
  customer_id BIGINT UNSIGNED NOT NULL, 
  product_id BIGINT UNSIGNED NOT NULL, 
  consumer_date DATE NOT NULL,
  consumer_number INT UNSIGNED NOT NULL DEFAULT 0, 
  create_ts TIMESTAMP(5) NOT NULL, 
  last_modifid_ts TIMESTAMP(5) NOT NULL, 

  PRIMARY KEY(customer_id, product_id, consumer_date), 
  CONSTRAINT fk_aggregation_customer_product FOREIGN KEY(product_id) REFERENCES product_master(product_id) ON UPDATE CASCADE, 
  CONSTRAINT fk_aggregation_customer FOREIGN KEY(customer_id) REFERENCES customer(customer_id) ON UPDATE CASCADE 
);

最後に

まだまだ経験不足なため、拙い設計となってしまっているかもしれませんが、
一通り動くものを作成して、サーバーで稼働させる経験を積みたいので、
世間に出回っている一般的な設計書を元に、今回のような設計を行いました。

現在は実装段階ですが、Spring Bootの動きが正確にわかっていないこともあり、
非常に時間がかかってしまっているため、在庫管理までの実装を行えないかもしれませんが、
設計した内容は見直す可能性があるので、本記事に備忘録として登録しました。

実装過程で修正/追加していくものもあると思うので、そちらの方も適宜更新します。

以上