【DB/SQL】データベースやらSQLやらで悩むこと

■ 0. はじめに

データベースやらSQLとかやらで、わからないなりに実装するかどうか悩んだことを書き連ねてみます。

◎ 併せて読み返したい書籍
『SQLアンチパターン』ISBN 978-4-87311-589-4
◎ 併せて読みたい記事
データベースオブジェクトの命名規約

■ 1. 命名に略語を用いる

・問題1
略語を用いてわかりやすくなるのか？ 混乱するだけじゃないのか？
・問題2
略語を用いる場合、その規約に従えば誰もが同じ略記をするような、明瞭な規約を掲げることができるのか？
・問題3
『一切略語を使用しない』『掲げた規約に準じた命名を行う』という選択肢は妥当であるように思われるが、
『略語は使って使わなくてもいいし、使うならご自由にどうぞ』という選択肢は妥協案として存在していいのか？

▼ 1.1. 要素の命名に略語を用いる

◎ 列fuga_codeという名前をつけたいとき、
⇒ fuga_codeではなく、fuga_cdとして命名する

【メリット】

・短い

【デメリット】

・略語の規則を関係者全員が知っていて、かつそれを厳守しなければ混乱を招く
・命名できる要素の種類が多いため、十全な略語の規約設定・周知にコストがかかる
（各種テーブル／ビュー／カラム／ユーザ定義関数・変数／インデックス／制約など）

俺はnameをnmとするぜ！ 私はnumberをnmにするわ！など、
個々の裁量に任せると却って分からなくなってしまいます。
他の開発言語で既に略記の規約が制定されているのならば、それに合わせればいいのだけれど、
最近は多少冗長でも一切略記をしないコーディング規約も多いらしいので悩むところ。

▼ 1.2. テーブル・カラムのエイリアス（別名）に略語を用いる

◎ 以下のような構造したSQL文を書くとき、

    SELECT
        voucher_number
    FROM
        hoge_fuga_piyo
    LEFT JOIN
        foo_bar_baz ...

⇒ テーブルに略語のエイリアスをつけて以下のようにする

    SELECT
        voucher_number AS vo_nm
    FROM
        hoge_fuga_piyo AS HFP
    LEFT JOIN
        foo_bar_baz AS FBB ...

【メリット】

・短い

【デメリット】

・略語の規則を関係者全員が知っていて、かつそれを厳守しなければ混乱を招く

ユーザが任意に省略するかを選択できる項目なので別途挙げました。
画一的な略語の規約を見つけたり設定するのってなかなか難しいのですが、
エイリアスをつけること自体は実行速度的に良いことらしいので命名していきたいところです。

■ 2. 列名の頭にテーブル名をつける

◎ テーブルhogeに列fugaという名前をつけたいとき、
⇒列を単にfugaではなく、hoge_fugaとして命名する

【メリット】

・わかりやすいかもしれない
・列名が絶対に重複しない
　→ 『…… is ambiguous』とか怒られることがなくなる

JOINするときなど、どちらのテーブルにも同名で存在する列を指定する場合、
テーブル名から指定してあげないと、どちらのテーブルの列を見ていいか分からず警告が出てしまいます。
けれど、そもそもテーブル名を省略して列名のみで指定しようとする書き方は
（例えばWHERE hoge.fuga = foo.barではなくテーブル名を省略してWHERE fuga = barとしてしまう書き方は）
排除すべきなのだろうから、重複しないことをメリットとして挙げるのも間違っているのかもしれません。

【デメリット】

・長い

テーブル名.列名で指定するのと、テーブル名つき列名で指定するんじゃ、
対して長さが変わらないじゃんと言われれば確かに変わらない気もします。
むしろ謎基準で命名された別名で、元テーブルを判断しなくて済むぶんはいいかもしれない。
だけどアンダーバーだけでしか区切れないと、やっぱわかりづらい。
それにサブクエリとか込み合ってくると混迷としてくるかも？
ちなみにNATURAL JOINが使えなくなります。

■ 3. テーブル名の頭に共通の文字列（接頭辞prefix）をつける

◎ テーブルhogeが存在するとき、
⇒トランザクション系ならt_を頭につけてt_hoge、
　マスタ系ならm_を頭につけてm_hoge、
　ワーク系ならw_を頭につけてw_hogeなどと命名する

⇒イベント系ならe_を頭につけてe_hoge、
　リソース系ならr_を頭につけてr_hoge、
　サマリ系ならs_を頭につけてs_hogeなどと命名する

【メリット】

・まとまっててわかりやすいかもしれない

テーブルが名前順でソートされているより機能->名前順でソートされていた方が、
分類方法認識の周知コストを加味した上でも分かりやすいと個人的には思います。

【デメリット】

・分類方法の認識が関係者全員で一致していて、かつそれを厳守しないと混乱を招く
・長い

上の例みたく2文字増えるだけでも、良かれ悪かれ、気になる人っていると思います。

◎ 併せて読みたい記事
イミュータブルデータモデル(入門編)

■ 4. 代理キー（サロゲートキー）

◎ 併せて読みたい記事
ナチュラルキーとサロゲートキーについての議論

▼ 4.1. 代理キーを使う

◎ 主キーにたくさんの列を使わないといけないとき（複合主キーのとき）
◎ 主キーにできる列（候補キー）が文字列型で、長ったらしい文字が入るかもしれないとき
◎ フレームワークでお手軽につくると勝手についちゃうとき
◎ 過去に辛い思いをして理想と現実の差を知ってしまったとき
⇒ そのテーブルの一番左に列をつくり通番を振って、振られた連番を主キーにする

AUTOINCREMENT, UNSIGNED, UNIQUE, NOTNULLなどといった属性を持たせて、
かつ原則的にUPDATE, INSERTのとき指定しない列をテーブルの左端にCREATEして主キーにします。

【メリット】

・WHERE句で指定しなければならない条件が少なくてすむ場合がある
・テーブル同士を紐付けるとき、お互いが持たなければならない列数が少なくなる
・代理キーは内部的な値なので、運用や仕様の変更があってもまず影響しない
　→自然キー（ナチュラルキー）だと、後々やっぱり重複させたくなるかもしれない

ただ、そのとき代理キーがあるから良かったねで済ませるべきなのか、
仕様が変わったんだから設計を見直すべきだとなるべきなのかは難しいところです。

【デメリット】

・関係モデルに存在しない概念を導入することになる

『関係モデルに存在しない概念を導入することになる』ことによるデメリットは、
どこか別の記事を参照してください。。。。

▼ 4.2. 代理キーを必ずつける

◎ テーブルを作成するとき
⇒ 必ず代理キーを主キーにする（主キーに含める）

【メリット】

・誰がテーブルを作成しても主キーはとりあえず存在する
→ テーブルを作成する人の、どこまでを自然キー、どこまでを代理キーという裁量が排除される

【デメリット】

・関係モデルに存在しない概念を導入することになる

■ 5. 削除フラグを用いる

◎ 表面上は削除扱いするけれど、内部的にはデータを保持しておきたいとき
⇒ 『is_deleted』みたいな列を用意して、0か1を入れる

もし使うときは必ずNOT NULL制約とデフォルト値を入れておく

【メリット】

・削除された列をもとに戻したい（削除を取り消したい）とき簡単に済む
　→ 削除を取り消す処理の実装が、かなり手抜きで済む
・代理キーの連番が1,2,4,……のように飛ばない（歯抜けにならない）

【デメリット】

・有効な列を取得したいとき、常にWHERE句に『is_deleted != 1』みたく書く必要がある
・レコード数が減らないのでテーブルがどんどん大きくなってしまう

『delete_flag』と命名して、
「その列が有効であるということが事実なんだから、削除された時は0(FALSE)を立てるべきだろ！」
「その列が削除されたということが事実なんだから、削除された時は1(TRUE)を立てるべきだろ！」
とか言い出す人が現れるかもしれない。。。そんなひとはいない？

■ 6. テーブル・カラムに拡張性をもたせる

◎ 将来に漠然とした不安を抱いてしまうとき
⇒『予備1』みたいな列をつくる
⇒ とりあえず列の大きさを(255)で定義する
⇒ とりあえず小数点以下の桁数を大きめに定義する
⇒ 外部キー, NOTNULL, UNIQUE, CHECKみたいな制約に対して消極的になる
⇒ 作成者、作成日時、更新者、更新日時をとりあえずつけておく

【メリット】

・『拡張してほしい』という要望が出て来づらくなる、出て来ても小手先で対応できるかもしれない

【デメリット】

・理想的な関係モデルによるメリットが得られなくなる

代理キーも削除フラグも、ある意味拡張性をもたせていると考えられるかもしれない。。。

■ 7. デフォルト値として空文字をつかう

◎ 必ずしも値が入らない文字列型の列があったとき
⇒ NOT NULL制約をつけてDEFAULT ''にする

【メリット】

・NULLの扱いで悩まずに済む
・データベース設計時に徹底してNULLを排斥しておけば、運用時に空文字とNULLが混在する事態にならない

【デメリット】

・そのスキーマにおいて徹底的にNULLを排斥しないと、空文字とNULLが混在して混沌と化す

明確な意図やルールなしに、なんとなくDEFAULT ''とするのはだめです。

■ 8. 区切られた識別子(Delimited Identifiers)をつかう

◎ 以下のような構造したSQL文を書くとき、

    SELECT
        voucher_number
    FROM
        hoge_fuga_piyo
    LEFT JOIN
        foo_bar_baz ...

⇒ 区切られた識別子をつけて以下のようにする (以下の例はMySQL)

    SELECT
        `voucher_number`
    FROM
        `hoge_fuga_piyo`
    LEFT JOIN
        `foo_bar_baz` ...

MySQLは`バッククォート、SQLServerは[]角括弧、
postgreSQLは"ダブルクォートなど言語によって違いがあります。

【メリット】

・エスケープ処理が適切ならば、SQLインジェクションを食い止めることができるかもしれない
・わかりやすいかもしれない

【デメリット】

・すべてのSQLにおいて漏れなく区切られた識別子をつけるのは地味にたいへん
・別のRDBMSに移植するとき少しだけ手間が増えるかもしれない