概要

試した忘備録として記録に残します。
内容は以下３点になります。
・単価について説明
・DDLでサンプルデータを定義
・DMLで想定される処理を再現

対象読者

販売管理システムの商品マスタについて知りたい方

商品の分類方法について

商品コードの桁を利用して、商品分類を表現する方法

メリット：一目でわかりやすい
デメリット：商品カテゴリを変更したい際にメンテナンスの手間が生じる

例　"0102003"
意味としては、大分類"01"　中分類"02" 商品コード"003"

商品分類マスタを使った方法

上の問題を解消するための処置。
新たに商品分類マスタを作成する。

実際の動き

図　テーブル設計　分類の図など

実現したいこと

１．階層１の「水産物」に分類される商品を取得したい。
２．階層０の「まぐろ」に分類される商品を取得したい。
３．商品名「まぐろトロ」の商品分類を取得する。
４．階層０「まぐろ」の上位分類を取得する。（階層１と２）

DDLとInsert文


drop table if exists  m_product_classification;
-- 1. 商品分類マスタ
-- 商品分類マスタは、商品の分類を管理するマスタです。
-- docker-compose exec -it webserver bash
-- sqlite3 03_hanbai/ch3-4_master/test.sqlite3  < 03_hanbai/ch3-4_master/product/m_products.sql
-- sqlite3 03_hanbai/ch3-4_master/test.sqlite3 "select * from m_products;"

create table  m_product_classification(
    code char(6) PRIMARY KEY,
    name varchar(50),
    layer int not null,
    superior_code char(6)
);


insert into m_product_classification values
('010000','生鮮食品',2,null),
('010100','食肉',1,'010000'),
('010101','牛肉',0,'010100'),
('010102','豚肉',0,'010100'),
('010200','水産物',1,'010000'),
('010201','まぐろ',0,'010200'),
('010202','えび',0,'010200')
;

drop table if exists m_products;

create table m_products(
    code char(5) PRIMARY KEY,
    official_name varchar(50),
    kana varchar(50),
    serial_number varchar(20),
    purchase_price int,
    sale_price int,
    tax_classification char(1),
    classification_code char(9) not null,
    updated_at timestamp not null,
    updated_by varchar(10) not null,
    remark varchar(100)
);

insert into m_products values
('00003','まぐろトロ','マグロトロ','1131',800,1200,'1','010201','2020-01-01 00:00:00','SYSTEM',""),
('00004','まぐろ赤身','マグロアカミ','1131',800,1200,'1','010201','2020-01-01 00:00:00','SYSTEM',""),
('00005','まぐろ刺身','マグロサシミ','1131',800,1200,'1','010201','2020-01-01 00:00:00','SYSTEM',""),
('00006','桜えび','サクラエビ','1131',800,1200,'1','010202','2020-01-01 00:00:00','SYSTEM',""),
('00007','ブラックタイガー','ブラックタイガー','1131',800,1200,'1','010202','2020-01-01 00:00:00','SYSTEM',""),
('00008','大正えび','タイショウエビ','1131',800,1200,'1','010202','2020-01-01 00:00:00','SYSTEM',"")
;

SQL文

-- 商品マスタ　商品分類
-- 実行コマンド
-- docker-compose exec -it webserver bash
-- sqlite3 03_hanbai/ch3-4_master/test.sqlite3 < 03_hanbai/ch3-4_master/product/getClassification.sql

DROP TABLE IF EXISTS logs;
CREATE TABLE logs(
    code varchar(2) PRIMARY KEY,
    message varchar(50)
);

insert into logs values
('1', '↓↓階層１「水産物」に分類される商品を取得する。↓↓'),
('2', '↓↓階層０「まぐろ」に分類される商品を取得する。↓↓'),
('3', '↓↓商品名まぐろトロ(00007)の階層0,1,2の商品分類を取得する。↓↓'),
('4', '↓↓階層０「まぐろ」の上位分類を取得する。（階層１と２）↓↓')
;

SELECT message FROM logs WHERE code = '1';

SELECT L1_class.name,L0_class.name,m_products.official_name
FROM m_products,m_product_classification as L0_class,m_product_classification as L1_class
WHERE m_products.classification_code = L0_class.code
AND L0_class.superior_code = L1_class.code
AND L1_class.name = '水産物'
;


SELECT message FROM logs WHERE code = '2';

SELECT  m_product_classification.name,m_products.official_name,m_products.kana
FROM m_products
JOIN m_product_classification
ON m_product_classification.code = m_products.classification_code
AND m_product_classification.name = 'まぐろ'
;


SELECT message FROM logs WHERE code = '3';
-- パフォーマンスは良くないはず
SELECT L2_class.name,L1_class.name,L0_class.name,m_products.official_name
FROM m_products,m_product_classification as L0_class,m_product_classification as L1_class,m_product_classification as L2_class
WHERE m_products.official_name = 'まぐろトロ'
AND m_products.classification_code = L0_class.code
AND L0_class.superior_code = L1_class.code
AND L1_class.superior_code = L2_class.code
;

SELECT message FROM logs WHERE code = '4';

SELECT parent.name,child.name,grandchild.name
FROM m_product_classification AS parent,m_product_classification as child, m_product_classification as grandchild
where grandchild.name = 'まぐろ'
and child.code = grandchild.superior_code
and parent.code = child.superior_code
;

DROP TABLE IF EXISTS logs;

結果


↓↓階層１「水産物」に分類される商品を取得する。↓↓
水産物|まぐろ|まぐろトロ
水産物|まぐろ|まぐろ赤身
水産物|まぐろ|まぐろ刺身
水産物|えび|桜えび
水産物|えび|ブラックタイガー
水産物|えび|大正えび
↓↓階層０「まぐろ」に分類される商品を取得する。↓↓
まぐろ|まぐろトロ|マグロトロ
まぐろ|まぐろ赤身|マグロアカミ
まぐろ|まぐろ刺身|マグロサシミ
↓↓商品名まぐろトロ(00007)の階層0,1,2の商品分類を取得する。↓↓
生鮮食品|水産物|まぐろ|まぐろトロ
↓↓階層０「まぐろ」の上位分類を取得する。（階層１と２）↓↓
生鮮食品|水産物|まぐろ

次やっておきたいこと

１つの商品分類から２つの商品分類に分割した場合もかんがえｒ

参考

グラス片手にデータベース設計　販売管理システム編　著者：梅田弘之 p51