yano3nora/db_architecture.md

## db_architecture.md

      
    Raw
  

              db_architecture.md
            
          
    DB設計のおいしさ

RDBMSを用いたWEBアプリケーションでは、その挙動のほとんどすべてがDBを経由する。よってER図や理論モデリングなどを参照し、DB・モデリングを理解した状態で開発するのが、アプリ開発の近道。
基本知識

行と列


行(レコード)：横のデータの組
列(カラム)：縦のデータの組

キー


主キー：一意にレコードを識別するためにある
外部キー：2つのテーブル間の列同士で設定する

制約


参照整合性制約：外部キーによる制約。

外部キー制約まとめ


UNIQUE 制約：ある列について一意性を求める制約。
CHECK 制約：ある列の取りうる値の範囲を制限する。
NOT NULL 制約：NULLを禁止する制約。

列には可能な限り NOT NULL 制約を付加する。NULL許可テーブルは使いづらい。


参考


https://www.ulsystems.co.jp/topics/006
https://www.ulsystems.co.jp/topics/007
https://goo.gl/0hgqwh

基本4大要素


エンティティ

DB格納する必要のある情報のまとまりのこと
UMLでは□で表現される


属性

エンティティに付随してわかる情報（カラム）のこと
UMLでは□内に・～～とリストで表現される


関連

エンティティ同士の結びつきのこと
UMLでは□同士を結ぶ線で表現される


関連の多重度

hasMany等の、より細かな関連の関係性のこと
UMLでは線に 1 --- * などと数字で表現される


DB正規化

モデリング・データモデリングを行う上で重要な【エンティティを可能な限り小さな単位にまとめる】こと。エンティティの属性に重複があったり、必要のない属性や関連があるとモデリングの障害となるので、常に必要最低限の最小単位にしておくことが望ましい。
正規化ポイント


更新時の不都合/不整合を排除するのが正規化
正規化をするためには、従属性を見ぬくこと
正規形はいつでも非正規形に戻せる(可逆的)
第三正規形までは原則として行うこと
関連エンティティが存在する場合は、関連とエンティティを1対1に対応させる

正規化手順

正規化には第一〜第五までの工程があり、一般的には第三正規化まで行い、パフォーマンスの関係から非正規化するべ気箇所があるか考察する。
第一正規化

全データをDBの基本テーブル構成（レコード毎に同じカラムのデータを持ち、タプル内の値は必ず単一）にする。簡単に言うとエクセルからDBに移す行為。
第二正規化

関数従属性のある項目について、部分従属であるものの主キーと値群を分割する。簡単に言うとテーブル内主キーを一つにする行為。
第三正規化

推移従属関係（主キー以外の外部キーと、その外部キーに対応している値群）を分割。簡単に言うと主キー以外の外部キーについてそのキー情報だけ保持して値を切り離す行為。
第四/ボイスコッド(4.5)/第五正規化

DBの(NULL)を許容する使い方ではほぼ不要。第四以降の正規化は「全ての情報が揃っていないと登録できない（NULLは入れられない）状態」を解決する。たとえばユーザ／メールアドレスみたいなものを別テーブルに分けてキーで結び付けたりする。(参考)
テーブル関連


1対1：１テーブルにまとめられるのであまり見ない
1対多：最もよくある関連のタイプ
多対多：多対多は原則作成してはならない。

(両者のエンティティが共通のキーとなる列を保持していないため、両エンティティを結合した情報を得ることができない)
存在する場合は1対多の関連に分解する


多対多の対処方法

関連エンティティ(交差テーブル)を作成することで、二つの1対多の関連に分解する

モデリング行程

コンセプトモデル（概念モデル）を考える

システムの目的、何を実現するのか？などを再検討する。エンティティ洗い出しや、その状態がどのように動くか考えて最終的にコンセプトモデル図やエンティティ状態偏移図が成果物になる。顧客に見せるレベルのもの。できれば「その場主義」で顧客と一緒に考えていくのが望ましい。
抽象化／継承アプローチ・分類アプローチ

コンセプトモデル設計でエンティティを洗いだす段階で、顧客から得られる情報は「現在のもの」がほとんど。DBを用いたアプリでは、実装後のDBモデル変更は大きな影響があるので、できれば将来性・拡張性のあるモデリングを提唱したい。そこで「エンティティの抽象化」を行い「将来変更があったときに柔軟に対応できる設計」にしておく。
継承アプローチによる抽象化

個人契約・法人契約について親クラスの「契約」エンティティを作成し、サブクラスとして「個人契約」「法人契約」を作成、「契約」がメインのエンティティなので将来「ファミリー契約」が出てきてもサブエンティティの追加だけに影響範囲を狭めることができる。オブジェクト的な考え方。理解しやすい代わりに、拡張の際はサブエンティティの追加が必要。
分類アプローチによる抽象化

契約エンティティをメインとし、その契約エンティティが他エンティティ（個人・法人）とどのような関連（個人契約をしている、法人契約をしている等）をしているのかを別のテーブルで管理する。フラグ・区分的な考え方。拡張の際はメインの管理エンティティ（分類テーブル）の拡張だけで済むので楽ちんだが、その分理解しづらくアプリケーションロジックは複雑になる。
時系列の考慮

エンティティの洗い出しでは「現在の情報」にばかり目がいき、「過去」「未来」の情報を保持しておきたいか？が抜けがち。あるメインエンティティについて、現在だけでなく時系列をまたいだ保存が必要ならば、メインエンティティの属性に「期間」という、”他エンティティと関連する「キー」”を設定する。当然ロジックは多少複雑になるので、必要のないものは時系列補完をするべきでない。
アイテムなのか？マスタなのか？

エンティティを洗い出す際に、ある具体的な一つのアイテム（商品在庫）なのか、そのアイテムを含むマスタ（商品種類）なのかがごちゃまぜになってしまいがち。扱う商品について、クラスなのかインスタンスなのかという点を確実にわけて関連をつけるべき。
論理モデルを設計する

まずはシステムの根幹となる機能について、システムの開発スコープ全体をカバーする「カタい」モデルまで設計する。
エンティティは常に複数の機能で利用されるため、根幹となる基本的なモデリングをあとで変更することは影響範囲が広くなりシステム全体のロジックがツギハギだらけになる。
最終的に論理ERモデル図やテーブル定義書、エンティティのライフサイクル定義書が出来上がる。開発者が見るレベルのもの。
エンティティの関連で用いる【キー項目】

コンセプトモデルを表現したざっくりUMLから、開発で必要なレベルまで「崩して落とし込んだ」ER図を作成するうえで、エンティティ（テーブル）同士の関連を示す項目（キー項目）を設計するのが一番のキモ。キー項目は「商品コード」や「ユーザID」など”ユーザに見えるもの（見せたいもの）”と”システム内部のみで利用するもの”の２種類ある。
依存関係と非依存関係

エンティティが「１対多」で結ばれるとき、【１側のエンティティなしでは存在しえない（多側の主キーは１側主キーを含む）】場合、これを「依存関係」と呼ぶ。反対に、【１側のエンティティがなくても存在しうる（多側の主キーは多側識別キーであり、属性に１側主キーをもつ）】場合は「非依存関係」となる。ER図でも依存関係は「同じライフサイクルをたどる」ことで実線で結ばれ、「別々のライフサイクルをたどる可能性がある」非依存関係は破線で結ばれまする。
テーブル分け

関連するテーブルを「継承アプローチ」でまとめようが「分類アプローチ」でまとめようが、結局やることは「１つのテーブルにまとめるか複数で管理するか」の２パターン。将来拡張する可能性が高いものはテーブルをできるだけ分け、拡張する可能性の低いものは同一テーブルにシンプルにまとめるのがよろしい。
導出項目と複写項目

テーブル分けが済んだら各エンティティ（テーブル）の属性を検討。この際に【ほかのエンティティの属性から算術・類推されるもの】を導出項目と呼ぶ。注文について、その商品の総和や税区分から算出される合計金額などがそれ。ビジネスロジック的に重要である場合は属性として保持し、そうでない場合は「必要な時に決まった形式で」導くのが良い。
あるエンティティの属性について、あるライフサイクルの時点で別エンティティに値をそのままコピーするような項目を複写項目と呼ぶ。カード番号などは「顧客」テーブルに保存されているが、ある注文の時点で「注文」テーブルに複写される。顧客のカード情報が将来変わっても、過去の注文テーブルには複写した古いカード情報が残るというもの。
物理モデルを実装する

サーバのスペックや、システム全体の機能性などを考慮しつつ、論理モデルを実際に実現するのに必要な設計を行う。テーブル名・項目名の定義や利用するRDBMS、サーバの選定なども含まれる。

テーブル名／項目名に物理名（英語名）を定義
属性のデータ型／桁数／精度を定義
パフォーマンスを考慮し「正規化崩し」を実施
検索効率を高めるための【集計表】の検討
アプリケーション仕様を加味した【一時表】の検討
運用要件／移行要件／DBMS製品の特性などを加味した参照整合性の定義

集計表

例えば「月別商品別売上額集計テーブル」のように集計ロジックを実行すれば導出可能ではあるが、業務的によく参照されるため、保存しておくテーブルのことです。つまり、テーブルまるごとが導出項目というテーブルです。
一時表(作業テーブル／ワークテーブル)

例えば他システムから受領するテキストファイルの内容を、ファイルレイアウトそのままの構造で、テーブルとして保持しておくようなテーブルです。つまり、設計の過程でシステムの「つくり」上の問題で必要になるテーブルのことを指します。

実践知識

CakePHPのアソシエーションを基準にしてみる

エンティティの数量関係


A hasOne Bの場合、Bは１つである
B belongsTo Aの場合も、Bは１つである
A hasMany Bの場合は、Bは複数である

外部キーの関係


A hasOne Bの場合、Bは外部キーを持っている
A belongsTo Bの場合は、Bは外部キーを持っていない
A hasMany Bの場合は、Bは外部キーを持っている。

まとめ


Bが１つ、外部キーを持ちたくない → B hasOne A
Bが１つ、外部キーを持ちたい → B belongsTo A
Bが複数 → A hasMany B
BもAも複数 → A HABTM B

マスター系テーブルをどう呼び込むか


Userエンティティ、ユーザステータス情報をまとめるStatusエンティティがある場合を考える
UserにStatusの外部キーを持たせて User hasOne Status というふうにまずは考えられる
この際、Statusエンティティには「一般会員・非会員・プレミアム会員」とか、「Admin権限・Manager権限・User権限」など、システム全体で共通化したい情報が別レコードに格納されることになる。（仮にAdminの名称をDevelopに変えようと思ったら、Status内の全レコードをいじる必要がある）
上記を回避するために俗に「マスタ」系とか呼ばれるテーブルを用意し、共通部分をまとめて管理する。Statusはいくつかの共通カラムについてStatusMasterの外部キーを保持する、みたいな感じ。
ここまでをまとめると User hasOne Status / StatusMaster hasMany Status であり Status belongsTo User / Status belongsTo StatusMaster のようなアソシエーションが出来上がる。
主従関係で言えばこの【ユーザはステータスを介してステータスマスタを一つ持つ】がベスト。だがしかし！この場合主従を入れ替えて【ステータスマスタが複数のユーザを持つ】にする方がシンプルかつ実践的である。どちらも問題なくデータをひっぱれるなら、よりシンプルな方が良い。
StatusMaster hasMany User / User belongsTo StatusMaster といったアソシエーションにして、【UserにStatusMasterの外部キーを持たせる】ことで要件を満たせる。

参考

アンチパターン

Jaywalk

ジェイウォーク（信号無視）→ authors(111,222,333) こんなやつ。（参考） 交差テーブル(中間テーブル)で対処する。唯一、ブログのタグ（文字列）など、ジェイウォークで格納するアイテムへアクセスしない(要はidを格納するんじゃなくて、数値や文字列を【列挙】する)場合のみ許される。
非スカラ値

配列型は利用しない。第一正規形を守る
ダブルミーニング

同一の列が二つの意味のデータを持つ (途中で格納するデータの種類が変更された) こと。列は変数ではない。一度意味を決めたら変更不可とすべき。
単一参照テーブル

同様のテーブル構造を持つマスタ群を一つにまとめたテーブルのこと。ダブルミーニングを一般化したもの。利点より欠点の方が大きい。
テーブル分割

I/Oコストを下げるために水平分割をしているケースがあるが、分割する意味的な理由がない。拡張性に乏しい。パーティションなど、他の代替手段がある。垂直分割もI/Oコストを下げるために用いられることがあるが、こちらも原則利用すべきではない。分割する意味的な理由がない。集約で代替が可能（列を絞り込んだテーブルを作成する／サマリーテーブルを作成するなど）
不適切なキー

キー (主キー、外部キー、結合キー) に使用される列に対するデータ型に、可変長文字列 (VARCHAR) は用いない。不変性を備えていない。固定長文字列と同値にならず、結合キーとして使いにくい。
ダブルマスタ

ダブルマスタとは、同じ役割を果たすはずのマスタテーブルが二つ存在するようなケース。システム統合で起きることが多いので、データクレンジングを行う。
代理キー

主キーが決められないパターンが存在します。

パターン1. そもそも入力データに主キーにできるような一意キーが存在しない
パターン2. 一意キーはあるが、サイクリックに使いまわされる
パターン3. 一意キーはあるが、途中で指す対象が変化する

このような問題を解決する手段として、代理キーが利用されます。代理キーとは、入力データに最初から存在しているキーの代理として新たに追加するキー。原則としては、代理キーによる解決は避けて、自然キーによる解決を図るべきです（代理キーが論理的には不要なキーで、論理モデルをわかりにくくしてしまうため）。自然キーだけで解決するためには、次のような方法を取ります。

パターン1. 業務仕様を調整するか、DBに投入される前にアプリケーションでデータが一意になるように整形する
パターン2. 履歴管理のための時間を表す列 (タイムスタンプとインターバル) を追加する
パターン3. パターン2と同じ

それでも代理キーを使わざる得ない場合、一意性と連続性が保証されるように、オートナンバリングを用いて実装します。
列持ちテーブル

配列を模倣するために、列持ちテーブル (繰り返し項目テーブル) を使用しているケース。拡張性の高い行持ちテーブルでデータを保持することが基本。パフォーマンス上の問題でどうしても列持ちテーブルが必要になった場合に、列持ちテーブルを作成する。
アドホック (場当たり的)な集計キー

アドホックな集計キーを追加することで、集計を簡単にできる一方、追加の度にテーブルが巨大になり、パフォーマンスが悪化します。そこで以下のような対策を取ります。

キーを別テーブルに分離する
ビューを使う
GROUP BY句の中でアドホックなキーを作成する

多段ビュー

ビューは有効な道具である一方、短所として、パフォーマンスへ悪影響を与えることと、濫用するとかえって設計と実装を複雑なものにしてしまうことが挙げられます。ビューにSQLからアクセスが行われたとき、ビュー定義のSELECT文を実行して、オリジナルのテーブルにアクセスしている。そのため、多段ビューを構築すると、パフォーマンスが悪化し、かつテーブルとビューの依存関係をわかりにくくするため、仕様が複雑になり管理が困難になる。ビューの使用は、原則として一段にとどめておくこと。

階層構造をRDBMSでどうするか


階層構造データへの挑戦

よくあるツリー構造／階層構造をSQLで云々カンヌンするのは、意外と手間。手法毎にメリット・デメリットあるので、そもそも木構造でいいのか？ってのもあわせて要検討。
あと利用する ORM ライブラリや FW によっては、木構造をサポートするライブラリがあったりするので検索してみたほうがいい。自前で組むと事故る。
隣接リスト型 （ナイーブツリー）


親 ID を子が抱えるアンチパターン
特定の親ノードから、全ての子孫ノードを取得する際に再起クエリが必要になる
階層が固定されていたり、再起クエリが使えたり、シンプルなデータ構成なら検討してもいい

閉包テーブル (クロージャテーブル)


RDBでツリー構造を表現する方法 > 閉包テーブル


祖先、子孫ノードの親子関係を中間テーブルで管理する手法
メリット

再起クエリがない RDBMS で、リレーションを SQL レベルで保ちたいケースで有用
検索・更新どちらにも強い


デメリット

更新時にトランザクション・ロックを考慮しないといけない
中間テーブルのレコード数、一度に UPDATE するレコードが膨大になる
直近の親・子の参照クエリが複雑になる


経路列挙モデル ( Path Enumration Model )


階層構造をＳＱＬで簡単に操作する


全エンティティに親からの絶対パスをもたせるモデル
メリット

設計と検索クエリ実装は楽


デメリット

検索パフォーマンスが悪い (なんせ文字列なんで)
整合性担保はアプリレベルでしかできない (なんせ文字列なんで)
更新の実装がしんどいので、更新されるデータには使わないほうがいい
サポートする深さは、パスを格納するカラムの最大長に依存する


入れ子集合モデル


入れ子集合モデル


Redmine でも使われてる、親子関係を円で表すやつ
クエリが直感的にイメージしづらいのと、ネストの深さがカラムの整数値最大長に依存する


TIPS

INDEX が使えない検索をしない

WHERE 句における LIKE %...% NOT LIKE NOT != BETWEEN は せっかく貼っているINDEXを使うことができない検索になる ため、走査対象が増えると性能劣化のもとになりやすい。もちろん利用する DB や「性能をどこまで求めたいか？」にもよるのでケースバイケースだが、上記のような検索を実装する前に 先に母数を絞ってから やるか 極力 IS / = / IN でまかなう 工夫をするべき。
id は int(11) ?

int勢の最大桁数は

型	バイト	(最小値)最大値
TINYINT(1byte)
-128    127
SMALLINT (2byte)
-32768    32767
MEDIUMINT (3byte)
-8388608    8388607
INT (4byte)
-2147483648    2147483647
BIGINT(8byte)
-9223372036854775808  9223372036854775807

int(11)ってなによ(MySQLの場合)

char(3)とかと違って、int(11)は桁数じゃありません。Mysql独特の方言で、列をゼロ埋め (UNSIGNED ZEROFILL)表示するときの桁数のこと。ゼロ埋めしてない状態でも、空白が入っていることになってる。あんま気にせんで(11)って書いとけ。
id(primary-key) は int(11) の unsigned ?

intは32bitの数値(2の32乗=42億位)を表現する。桁数が整数値(+値)では21億ちょい。UNSIGNED 属性をつけるとマイナス値で用意してるもう半分をプラス値で使える(要は21->42億までつくれるようになる)。
id を bitint(20) にしてみる

int(11) だと足りなくね？但し bitint(20) は演算するカラムに使う場合は留意点があるので注意（ PRIMARY KEY で利用する場合は演算しないから大丈夫だけどね）。あと UNSIGNED もつけといてね。参考
インデックス貼るときの指針

B-treeインデックス? 参考

大規模になりそうなテーブルか？

データ量が少ない（レコード数が1万件以下）場合は効果が薄い


カーディナリティが高いカラムか？

カーディナリティ：特定の列の値が、どのくらいの種類を多さを持つかということを表す概念


カラムの値が平均的に分散しているか？（ hoge_code のような）

フラグや区分に貼ると逆効果 → 参考


WHERE / JOIN の「条件」に使用されているカラムか？

注意点


主キーおよび一意制約の列にはインデックスは不要
B-treeインデックスは更新性能を劣化させる


## path_enum_model.sql
-- 経路列挙メモ
---- Expression and Control of tree map by Path Enumeration Model with PostgreSQL.
---- refs: https://goo.gl/8hqfZf

-- サンプルデータ
---- テーブル
CREATE TABLE department
(
  departmentcode character varying NOT NULL,
  departmentname character varying,
  path character varying,
  CONSTRAINT department_pkey PRIMARY KEY (departmentcode ),
  CONSTRAINT department_departmentcode_check CHECK (replace(departmentcode::text, '/'::text, ''::text) = departmentcode::text)
)

---- シード
insert into department values ('1', '本社',   '/1/');
insert into department values ('2', '東京',   '/1/2/');
insert into department values ('3', '大阪',   '/1/3/');
insert into department values ('4', '豊島区', '/1/2/4/');
insert into department values ('5', '中野区', '/1/2/5/');
insert into department values ('6', '東池袋', '/1/2/4/6/');
insert into department values ('7', '難波',   '/1/3/7/');
insert into department values ('8', '難波下',   '/1/3/8/');


-- INSERT
---- 末端追加
insert
  into department
  values (
    :new_departmentCode, :new_departmentName, (
      select path
        from department
        where departmentCode = :parent
    ) || :new_departmentCode || '/'
  )

---- 中間追加
insert
  into department
  values (
    :new_departmentCode, :new_departmentName, (
      select path
        from department
        where departmentCode = :parent
    ) || :new_departmentCode || '/'
  )
------ 以下、挿入した位置の子供分プログラムで繰り返す
------ 孫がいればさらに再帰して実行する
update department
  set path = replace(
    path, '/' ||
    :child ||
    '/', '/' ||
    :new_departmentCode ||
    '/' ||
    :child ||
    '/'
  )
  where path like '%/' || :child || '/%'


-- DELETE
---- 中間削除 → 上詰め
------ 純削除して...
delete
  from department
  where departmentCode = :departmentCode;
------ 上に詰める... ( ルートの削除はできない仕様 )
update department
  set path = replace(path, '/' || :departmentCode || '/', '/')
  where path like '%/' || :departmentCode || '/%';

---- 配下を含め全削除
delete
  from department
  where path like (
    select path
      from department
      where departmentCode = :department_code
  ) || '%';


-- SELECT
---- ルート取得
select *
  from department
  where departmentCode = replace(path, '/', '');

---- 末端リーフ取得
------ 「パスに自分以下のパスを持つ」レコードを not exists で除外
select *
  from department Parent
  where not exists (
    select *
      from department Children
      where Children.path like Parent.path || '_%'
  )

---- 深さを取得
select departmentCode, length(path) - length(replace(path, '/', '')) -1 as level
  from department;

---- 直近の親リーフ取得
select children.departmentName, parent.departmentName
  from department children left
    join department parent on children.path > parent.path
      and parent.path = (
        select max(path)
        from department
        where children.path like path || '_%'
      )

---- 直近の子リーフ取得
------ 自身パスを含むパスをもつ子孫群の中でパスが最大
select parent.departmentName oya, children.departmentName ko
  from department parent left
    join department children on parent.path = (
      select max(path)
      from department
      where children.path like path || '_%'
    )

---- ツリーの中の特定のツリー取得（自身含む）
select parent.departmentName, children.departmentName
  from department parent, department children
  where children.path like parent.path || '%'
    and parent.departmentName = '東京'
    order by parent.path
	-- 経路列挙メモ
	---- Expression and Control of tree map by Path Enumeration Model with PostgreSQL.
	---- refs: https://goo.gl/8hqfZf

	-- サンプルデータ
	---- テーブル
	CREATE TABLE department
	(
	departmentcode character varying NOT NULL,
	departmentname character varying,
	path character varying,
	CONSTRAINT department_pkey PRIMARY KEY (departmentcode ),
	CONSTRAINT department_departmentcode_check CHECK (replace(departmentcode::text, '/'::text, ''::text) = departmentcode::text)
	)

	---- シード
	insert into department values ('1', '本社', '/1/');
	insert into department values ('2', '東京', '/1/2/');
	insert into department values ('3', '大阪', '/1/3/');
	insert into department values ('4', '豊島区', '/1/2/4/');
	insert into department values ('5', '中野区', '/1/2/5/');
	insert into department values ('6', '東池袋', '/1/2/4/6/');
	insert into department values ('7', '難波', '/1/3/7/');
	insert into department values ('8', '難波下', '/1/3/8/');


	-- INSERT
	---- 末端追加
	insert
	into department
	values (
	:new_departmentCode, :new_departmentName, (
	select path
	from department
	where departmentCode = :parent
	) \|\| :new_departmentCode \|\| '/'
	)

	---- 中間追加
	insert
	into department
	values (
	:new_departmentCode, :new_departmentName, (
	select path
	from department
	where departmentCode = :parent
	) \|\| :new_departmentCode \|\| '/'
	)
	------ 以下、挿入した位置の子供分プログラムで繰り返す
	------ 孫がいればさらに再帰して実行する
	update department
	set path = replace(
	path, '/' \|\|
	:child \|\|
	'/', '/' \|\|
	:new_departmentCode \|\|
	'/' \|\|
	:child \|\|
	'/'
	)
	where path like '%/' \|\| :child \|\| '/%'


	-- DELETE
	---- 中間削除 → 上詰め
	------ 純削除して...
	delete
	from department
	where departmentCode = :departmentCode;
	------ 上に詰める... ( ルートの削除はできない仕様 )
	update department
	set path = replace(path, '/' \|\| :departmentCode \|\| '/', '/')
	where path like '%/' \|\| :departmentCode \|\| '/%';

	---- 配下を含め全削除
	delete
	from department
	where path like (
	select path
	from department
	where departmentCode = :department_code
	) \|\| '%';


	-- SELECT
	---- ルート取得
	select *
	from department
	where departmentCode = replace(path, '/', '');

	---- 末端リーフ取得
	------ 「パスに自分以下のパスを持つ」レコードを not exists で除外
	select *
	from department Parent
	where not exists (
	select *
	from department Children
	where Children.path like Parent.path \|\| '_%'
	)

	---- 深さを取得
	select departmentCode, length(path) - length(replace(path, '/', '')) -1 as level
	from department;

	---- 直近の親リーフ取得
	select children.departmentName, parent.departmentName
	from department children left
	join department parent on children.path > parent.path
	and parent.path = (
	select max(path)
	from department
	where children.path like path \|\| '_%'
	)

	---- 直近の子リーフ取得
	------ 自身パスを含むパスをもつ子孫群の中でパスが最大
	select parent.departmentName oya, children.departmentName ko
	from department parent left
	join department children on parent.path = (
	select max(path)
	from department
	where children.path like path \|\| '_%'
	)

	---- ツリーの中の特定のツリー取得（自身含む）
	select parent.departmentName, children.departmentName
	from department parent, department children
	where children.path like parent.path \|\| '%'
	and parent.departmentName = '東京'
	order by parent.path