対象読者

販売管理システムの取引先マスタのテーブル設計について知りたい方

概要

会社規模、業種といった分類で取引先の売上を確認したい場合があるとします。

上記の機能を実現するために、取引先分類マスタを新規追加します。
取引先分類マスタを新規追加することで既存のテーブルとレコードを変更することなく、分類ごとの情報の取得を可能とします。

用語

業種

企業が扱っている商品や業務の種類の大分類

例としては、建設業、製造業、情報通信業、不動産

業態

営業方法。

例としては、
小売の場合
デパート、スーパーマーケット、コンビニ、ディスカウントストア、ドラッグストア

情報通信業の場合、
自社製品、SES,受託開発

参考リンク
https://www.biznavi.jp/corporation/779

例

実現したいこと

・取引先コード001(林商店)の業態、規模を取得したい。

・取引先コード002（クリエイト）の業種、規模を取得したい

テーブルの構造

テーブル名	説明
取引先分類種別マスタ	階層構造の親部分。分類の項目部分
取引先分類マスタ	階層構造の子部分。分類の値部分
取引先分類所属マスタ	取引先の分類をまとめたテーブルレコード数は取引先マスタ×取引先分類種別マスタ軒分
取引先マスタ	取引先名を参照するために利用

DDLとInsert文

-- 1. 取引先（顧客　仕入先）マスタ
-- docker-compose exec -it webserver bash
-- sqlite3 src/03_hanbai/ch3-4_master/test.sqlite3  < src/03_hanbai/ch3-4_master/customer/ddl_class.sql
-- sqlite3 src/03_hanbai/ch3-4_master/test.sqlite3 "select * from m_client_classification_types;"


--取引先分類種別コード
--
drop table if exists  m_client_classification_types;
create table m_client_classification_types(
    classification_code char(2) PRIMARY KEY,
    name varchar(50),
    updated_at timestamp default current_timestamp,
    updated_by varchar(50)
);

insert into m_client_classification_types values
('01','業種','','admin'),
('02','業態','','admin'),
('03','規模','','admin'),
('04','会社形態','','admin'),
('05','上場','','admin')
;

-- 取引先分類マスタ
drop table if exists m_client_classifications;
create table m_client_classifications(
    classification_code char(3) not null,
    type_code char(2)  not null,
    name varchar(50),
    updated_at timestamp default current_timestamp,
    updated_by varchar(50),
    primary key(classification_code,type_code)
);

insert into m_client_classifications values
('001','01','製造業','','admin'),
('002','01','商社','','admin'),
('003','01','卸売業','','admin'),
('004','01','小売業','','admin'),
('005','01','サービス業','','admin'),
('006','01','金融業','','admin'),
('007','01','不動産業','','admin'),
('008','01','その他','','admin'),
('001','02','個人商店','','admin'),
('002','02','スーパーマーケット','','admin'),
('003','02','コンビニ','','admin'),
('004','02','ディスカウントストア','','admin'),
('005','02','ドラッグストア','','admin'),
('006','02','自社製品','','admin'),
('007','02','SES','','admin'),
('008','02','受託開発','','admin'),
('001','03','１００人未満','','admin'),
('002','03','１００人以上５００人未満','','admin'),
('003','03','５００人以上','','admin'),
('001','04','株式会社','','admin'),
('002','04','合同会社','','admin'),
('003','04','合資会社','','admin'),
('004','04','合名会社','','admin'),
('001','05','プライム市場','','admin'),
('002','05','スタンダード市場','','admin'),
('003','05','グロース市場','','admin'),
('004','05','TOKYO PROマーケット','','admin'),
('005','05','上場してない','','admin')
;





-- 取引先分類所属マスタ
drop table if exists m_client_belong_classifications;
create table m_client_belong_classifications(
    type_code char(2)  not null,
    classification_code char(3) not null,
    client_code char(5) not null,
    updated_at timestamp default current_timestamp,
    updated_by varchar(50),
    primary key(classification_code,type_code,client_code)
);


insert into m_client_belong_classifications values
-- 林商店の情報について
('01','004','00001','','admin'),
('02','001','00001','','admin'),
('03','001','00001','','admin'),
('04','004','00001','','admin'),
('05','005','00001','','admin'),
--クリエイトの情報について
('01','004','00002','','admin'),
('02','005','00002','','admin'),
('03','003','00002','','admin'),
('04','001','00002','','admin'),
('05','001','00002','','admin');




drop table if exists  m_clients_class;
create table m_clients_class(
    code char(5) not null,
    name varchar(50) not null,
    kana varchar(50)
);

insert into m_clients_class values
('00001','林商店','ハヤシショウテン'),
('00002','クリエイト','クリエイト'),
('00003','地元密着スーパー','ジモトミッチャクスーパー')
;

SQL文


-- docker-compose exec -it webserver bash
-- sqlite3 src/03_hanbai/ch3-4_master/test.sqlite3  < src/03_hanbai/ch3-4_master/customer/getClass.sql


--取引先コード00001(林商店)の業態(02)、規模(03)を取得したい。
-- SQLとしていかん気がする
SELECT "↓↓↓取引先コード00001(林商店)の業態(02)、規模(03)を取得したい。↓↓↓";
SELECT DISTINCT m_client_belong_classifications.client_code,C.name,A.name,B.name
FROM m_client_belong_classifications
INNER JOIN m_client_classifications as A
INNER JOIN m_client_classifications as B
INNER JOIN m_clients_class as C
WHERE m_client_belong_classifications.client_code = '00001'
    AND A.type_code = '02'
    AND B.type_code = '03'
    AND m_client_belong_classifications.classification_code= A.classification_code
    AND m_client_belong_classifications.classification_code= B.classification_code
    AND C.code =m_client_belong_classifications.client_code;
;



--取引先コード00002（クリエイト）の業種(01)、規模(03)を取得したい
-- 一レコードで取得したい
SELECT "↓↓↓取引先コード00002（クリエイト）の業種(01)、規模(03)を取得したい↓↓↓";
SELECT A.client_code,E.name,B_1.name,C_1.name,D_1.name
FROM m_client_belong_classifications as A
-- 規模
INNER JOIN m_client_belong_classifications as B
ON A.client_code = B.client_code
AND B.type_code = '03'
INNER JOIN m_client_classifications as B_1
ON B.classification_code = B_1.classification_code
AND B.type_code = B_1.type_code
-- 業種
INNER JOIN m_client_belong_classifications as C
ON A.client_code = C.client_code
AND C.type_code = '01'
INNER JOIN m_client_classifications as C_1
ON C.classification_code = C_1.classification_code
AND C.type_code = C_1.type_code
-- 会社形態
INNER JOIN m_client_belong_classifications as D
ON A.client_code = D.client_code
AND D.type_code = '04'
INNER JOIN m_client_classifications as D_1
ON D.classification_code = D_1.classification_code
AND D.type_code = D_1.type_code
--会社名
INNER JOIN m_clients_class as E
ON A.client_code = E.code
WHERE  A.client_code = '00002'
GROUP BY A.client_code
;

結果

root@b5921a0e5caa:/# sqlite3 src/03_hanbai/ch3-4_master/test.sqlite3  < src/03_hanbai/ch3-4_master/customer/getClass.sql
↓↓↓取引先コード00001(林商店)の業態(02)、規模(03)を取得したい。↓↓↓
00001|林商店|個人商店|１００人未満
↓↓↓取引先コード00002（クリエイト）の業種(01)、規模(03)を取得したい↓↓↓
00002|クリエイト|５００人以上|小売業|株式会社

一言

取得するSQLを書くのに手間どりましたね。
もっと上手い書き方あると思います。
まだ自分のSQL力は足りないと実感しました。

すべての分類を網羅したビューを作った方が、使い勝手良さそうだと思いました。

参考

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

github更新差分