2015-03-15

在庫管理のこと〜概要

ロジスティクス

棚卸とか倉庫移動とかいうキーワードに弱いので勉強を始めたぞい。

SEがはじめて学ぶ在庫管理

作者: ITC総合研究所
出版社/メーカー: 日本実業出版社
発売日: 2009/09/10
メディア: 単行本（ソフトカバー）
購入: 3人クリック: 23回
この商品を含むブログ (1件) を見る

書店で見つけてパラ見したらかなり良さそうな内容だったので買ってみた。用語は慣れないところがあるけれども、在庫に対する考え方はわかりやすくてよい。

在庫管理とは何か

在庫（将来的に売ることを目的として自社で保有しているモノ）を正確かつ適正に管理をすること

なぜ（正確かつ適正に）在庫管理するのか

利益を上げるため

在庫が不足（欠品）すると...

商品を買いたい人が買えない
商品が無い店だと思われて客足が遠のく
納期を守れない会社だと思われて受注が減る

在庫があれば手に入るはずのお金が消えてなくなり、将来的な売上まで減ってしまう

在庫が過剰になると…

売ってもすぐに無くならないため、商品の鮮度（価値）が下がる
無駄に場所をとるため、維持コストがかかる
商品がリニューアルしたときには売れなくなって余ってしまう
お金へとすぐに変えられないため、投資家から経営効率の悪い会社とみなされて敬遠される

在庫は多すぎても少なすぎてもダメ。

お客さんが欲しいモノを　欲しいときに　欲しい分量だけ、届けることができるように在庫は正確かつ適正に管理しなくてはいけない。そのために、需要の先読みから始まって、商品の生産→売り場への到着までの時間やコストの計算が必要になる。

これが数千レベルになってくると正直考えただけでもお手上げな感じだし、多くの企業で在庫管理がうまくいかないって言われているだけある。逆に、在庫管理に成功する（＝無駄が削減できている）なら、他の会社との競争でも優位に立てる。

2015-03-13

SQLアンチパターンその７　マルチカラムアトリビュート

６章がちょっと書きにくいので、とばします！

マルチカラムアトリビュートとは

「複数列の属性」の名前の通り、１つのレコードの中で「２つ目の住所」「３つ目のメールアドレス」のような複数存在する可能性がある属性のこと。テーブルの列数は可変にできないけれど、一体いくつ用意しておけばいいのか予測不能！

CREATE TABLE Customer (
  customer_id      INT UNSIGNED NOT NULL,
  name            VARCHAR(1000),
  address1        VARCHAR(20),
  address2        VARCHAR(20),
  address3        VARCHAR(20),
  ...
  PRIMARY KEY (customer_id)
);

いつ起こるのか

テーブルを設計するときに、属性として「複数」登録できそうな列があったとき。いくつまで用意しておけばいいのかと思ったら怪しい。

何をしてはいけないのか（アンチパターン）

複数の列を定義すること。

何故ダメなのか

上限を超えたときに詰む

言わずもがな

マルチカラムアトリビュートの中から特定の値を検索するときのSQLが不便

SELECT * 
FROM Customer
WHERE address1 = 'sample@gmail.com'
   OR address2 = 'sample@gmail.com'
   OR address3 = 'sample@gmail.com'

属性に値を追加・更新・削除するときにはもっと面倒なことになる
address1, address2, address3に空きがあるか？
空いている列に値を入れようとしている間に別のユーザが更新している可能性は？
追加・更新された値がレコード内で一意になっているか？

SQLが複雑になること必至

いい解決策は

従属テーブルを作る

CREATE TABLE Customer (
  customer_id      INT NOT NULL,
  name            VARCHAR(1000),
  address1        VARCHAR(20),
  address2        VARCHAR(20),
  address3        VARCHAR(20),
  ...
  PRIMARY KEY (customer_id)
);


CREATE TABLE Customer_Address (
  customer_id     INT NOT NULL,
  address        VARCHAR(20) NOT NULL,
  PRIMARY KEY (customer_id, address),
  FOREIGN KEY ( customer_id ) REFERENCES Customer(customer_id) ON DELETE CASCADE ON UPDATE CASCADE,
);

これで上限気にせずに追加ができるし、検索も楽

SELECT *
FROM Customer AS c
INNER JOIN Customer_Address AS ca ON c.customer_id = ca.customer_id
WHERE c.address = 'sample@gmail.com'

「列増やすなら行増やせ」

2015-02-27

SQLアンチパターンその５　EAV（エンティティ・アトリビュート・バリュー）

SQL

EAV（エンティティ・アトリビュート・バリュー）とは

Attribute（属性）とValue（値）のペアを１行として登録すること。

CREATE TABLE Products (
  product_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(100),
  PRIMARY KEY (product_id)
);

CREATE TABLE Product_attributes (
  product_id BIGINT UNSIGNED NOT NULL,
  attribute_name VARCHAR(100) NOT NULL,
  attribute_value VARCHAR(100),
  PRIMARY KEY (product_id, attribute_name),
  FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

(product_id, name)
------------------
(1, "hogehoge")

(product_id, attribute, value)
------------------------------
(1, "brand", "NB")
(1, "country", "japan")
(1, "custom1", "piyo")

いつ起こるのか

可変属性のサポート。テーブルの設計変更を柔軟にしようとする

何をしてはいけないのか（アンチパターン）

汎用的な属性（EAV）テーブルを作ること

何故ダメなのか

属性値の取得クエリの明確さが低下する。

-- 従来のやりかた
SELECT product_id, brand
FROM Products;

-- EAVでのやりかた
SELECT product_id, attribute_value AS brand
FROM Product_attributes
WHERE attribute_name = 'brand';

適切なデータ型が使用できなくなる。
NOT NULL制約、CHECK制約、FOREIGN KEY制約が使えなくなる。

可変な属性に対応すると、どんな値が入っても良い＝列ごとに設定するべき制約を設定できなくなる。

本来１行だった行を作り直すのが大変。

SELECT p.product_id,
       a1.attribute_value AS brand,
       a2.attribute_value AS country,
       a3.attribute_value AS custom1
FROM Products AS p
    LEFT OUTER JOIN Product_attributes AS a1
        ON p.product_id = a1.product_id AND a1.attribute_name = 'brand'
    LEFT OUTER JOIN Product_attributes AS a2
        ON p.product_id = a2.product_id AND a2.attribute_name = 'country'
    LEFT OUTER JOIN Product_attributes AS a3
        ON p.product_id = a3.product_id AND a3.attribute_name = 'custom1'
WHERE p.product_id = 1;

いい解決策は

テーブル継承
準構造化データ
アプリで頑張る

テーブル継承には以下の方法がある。いずれもOOPの継承の概念をテーブルで表現したもの。

単一テーブル継承（STI ... Single Table Inheritance）
具象テーブル継承
クラステーブル継承

単一テーブル継承は、サブクラスのメンバ（列）をすべて一つのテーブルに含む方法。共通部（親クラスメンバ）を除き、行毎にタイプが異なるため、ある行で値を持たない列にはNULLが入る。

具象テーブル継承は、サブタイプ毎にテーブルを作成し、共通列もそれぞれで持つ方法。

クラステーブル継承は、サブタイプのメンバだけを持つテーブルを新たに作り、親テーブルからの参照を張る方法。これは垂直分割であり、テーブルの正規化をするときの方法。

準構造化データ JSONとかXML形式のデータをBLOB型の列として持つ。アプリ側でパースして、内容のチェックが必要になるため、DBに頼らずアプリで頑張る方法。
アプリで頑張るフレームワークによってはEAVを使っている場合もあるし、使わざるを得ない場合もある。その時は型チェックとか諸々をアプリで頑張る。

NULLの話も後で出てくるんですが、新たな属性が必要になったときは列を追加する＝STIでいく　のがよいかと。ワイルドカードを使わない、NULLの扱い方をちゃんと意識するというのを守れていればーですけど。

2015-02-24

SQLアンチパターンその４　キーレスエントリ（外部キー嫌い）

SQL

SQL アンチパターンは全部で25章あるので、毎日書いても１ヶ月かかる内容だった。なので、今後はそんなにがっつり書かずにポイントだけ。

キーレスエントリ（外部キー嫌い）とは

外部キー制約を使わない

いつ起こるのか

テーブル間で参照をするとき

何をしてはいけないのか（アンチパターン）

外部キー制約を使わない

何故ダメなのか

外部キー制約は使われず、でも外部キーとして参照されている列がある場合、アプリケーションコード側で完璧にお守りをしてあげないと迷子になる行が出てくる。アプリが完全であることを前提にしてはいけない。

いい解決策は

外部キー制約使え

参照関係をそもそも壊さない仕組みだから、外部キー制約使うべき。

マスタになるキーを更新する際、ON DELETE CASCADE, ON UPDATE CASCADEとか使えば、外部キーとして参照しているテーブル側もちゃんと更新なり削除がかかる。知らなかった。これ覚えておくべき。

外部キー使う場合、たしかにオーバーヘッドになり得るけれど、アプリケーションコード側で事前チェックとか気にする必要なくなるし、DB側でできる部分は無理にアプリケーションに持ってくること無い。

2015-02-24

ファイル内の重複行削除

.net framework

ファイル内の重複している行を削除する。

fileRowDeduplicator

LinqのDistinctメソッドを使うとキレイにできる。

追記： kotyさんに教わった部分を入れてみて、FileUtility2.csとして投稿。かなりスッキリ

Enumerable.Distinct(TSource) メソッド (IEnumerable(TSource)) (System.Linq)

2015-02-22

SQLアンチパターンその３　IDリクワイアド（とりあえずID）

SQL

IDリクワイアド（とりあえずID）とは

名前のまま、「とりあえずID列作っておこうぜ」ということ

いつ起こるのか

主キーを作るルールがあるとき

何をしてはいけないのか（アンチパターン）

どんなテーブルに対しても「ID」という名前の列（サロゲートキー）を作ること

例：店で販売する商品

CREATE TABLE Goods(
  id            SERIAL  PRIMARY KEY,
  product_id    BIGINT,
  name          NVARCHAR(20),
);


(id, product_id, name)
----------------------------------
(1, 1014, "レッドブル エナジードリンク 缶185ml")
(2, 1237, "ライジン 缶185ml")
(3, 1935, "モンスターカオス 缶355ml")
(4, 2084, "リゲイン エナジードリンク 缶190ml")
(5, 2365, "バーン エナジードリンク 缶250ml")
...

何故ダメなのか

冗長な列ができてしまう

まさに上の例とかそうだけど、他の列を自然キーとして扱えるのならば、あえてid列を追加する必要は無い。

いい解決策は

「id」以外の名前をつける

「商品コード」とか、「user_id」みたいなわかりやすい名前を付けることを心がけること。交差テーブル作った時に、交差する両方のテーブルの主キーが「id」だと交差テーブルが扱いにくくなる。

自然キーで使えそうなものって、JANコードとかかなーと思ったんですが、再利用される可能性あるし、見極め難しそう。サロゲートキー使うでも良さそうだし、ケースバイケースだろうなと。

2015-02-20

SQLアンチパターンその２　ナイーブツリー（素朴な木）

SQL

ナイーブツリー（素朴な木）とは

「素朴な」という訳がぴんとこなかった… 「単純、未熟な」（思慮から生まれる）ツリー構造。

いつ起こるのか

表の構造で、ツリー（階層）構造を格納しようとするとき

何をしてはいけないのか（アンチパターン）

階層として「親への参照だけ」を持つこと。

例：会社の組織図を表すテーブルを作る

CREATE TABLE Departments(
  department_id  SERIAL   PRIMARY KEY,
  parent_id          INT,
  name                NVARCHAR(20),
  FOREIGN KEY  (parnt_id) REFERENCES Departments(department_id)
);


(department_id, parent_id, name)
----------------------------------
(1, NULL, "本社")
(2, 1, "A本部")
(3, 1, "B本部")
(4, 2, "A営業部")
(5, 2, "A開発部")
(6, 3, "B営業部")
...

何故ダメなのか

階層の探査をするクエリが非効率的。1階層下の子ノードの集合を取るのには、以下の様なクエリが必要。

SELECT d1.*, d2.*
FROM Departments d1
  LEFT OUTER JOIN Departments d2
    ON d2.parent_id = d1.department.id;

これがn階層になると、d1., d2., ... dn.* となり、もちろん階層分だけJOINすることになる。

中間のノードの削除を行う際に、親子関係を適切に組み替えるためのクエリも複雑になる。

いい解決策は

閉包テーブル（Closure Table）を作る

あるノードを取り上げた時、そのノードのすべての祖先と自分に対しての組み合わせを、行として格納する。先ほど挙げたDepartmentsテーブルに対して考えると、以下のようになる。

CREATE TABLE TreePaths(
  ancestor       INT  NOT NULL,
  descendant  INT  NOT NULL,
  PRIMARY KEY (ancestor, descendant),
  FOREIGN KEY (ancestor) REFERENCE Departments(department_id),
  FOREIGN KEY (descendant) REFERENCE Departments(department_id)
);

(ancestor, descendant)
--------------------------
(1, 1)
(1, 2)
(1, 3)
(2, 2)
(2, 4)
(2, 5)
(3, 3)
(3, 6)
(4, 4)
(5, 5)
(6, 6)

閉包テーブルを作ると、A本部の部署を取得する場合、以下のようなクエリでいい。

SELECT d.*
FROM Departments d
  INNER JOIN TreePaths As t ON d.department_id = t.ancestor
WHERE t.descendant=3

中間のノードの削除を行う場合でも、全ての祖先への参照が格納されているため、消したいノードのdepartment_idの行だけ消せばいい。

blog.toxn

あしあと

在庫管理のこと〜概要

在庫管理とは何か

なぜ（正確かつ適正に）在庫管理するのか

在庫が不足（欠品）すると...

在庫が過剰になると…

SQLアンチパターンその７　マルチカラムアトリビュート

マルチカラムアトリビュートとは

いつ起こるのか

何をしてはいけないのか（アンチパターン）

何故ダメなのか

いい解決策は

SQLアンチパターンその５　EAV（エンティティ・アトリビュート・バリュー）

EAV（エンティティ・アトリビュート・バリュー）とは

いつ起こるのか

何をしてはいけないのか（アンチパターン）

何故ダメなのか

いい解決策は

SQLアンチパターンその４　キーレスエントリ（外部キー嫌い）

キーレスエントリ（外部キー嫌い）とは

いつ起こるのか

何をしてはいけないのか（アンチパターン）

何故ダメなのか

いい解決策は

ファイル内の重複行削除

SQLアンチパターンその３　IDリクワイアド（とりあえずID）

IDリクワイアド（とりあえずID）とは

いつ起こるのか

何をしてはいけないのか（アンチパターン）

何故ダメなのか

いい解決策は

「id」以外の名前をつける

SQLアンチパターンその２　ナイーブツリー（素朴な木）

ナイーブツリー（素朴な木）とは

いつ起こるのか

何をしてはいけないのか（アンチパターン）

何故ダメなのか

いい解決策は

閉包テーブル（Closure Table）を作る