命名法 - 主キーはサロゲートキーで、テーブル名はナチュラルキーで(笑)
完全に新規の案件というのは本当に少ないので、実践できることはほぼない理想論ですが、私の理想とするテーブル構造と命名法です。
まずは、サロゲートキーについて
サロゲートキーというのは業務上意味のないキーのことです。
例えば、生徒テーブルは、年次・クラスID・生徒番号で一意になるとしましょう。
この「年次・クラスID・生徒番号」の複合キーを主キーとせずに、システム側で採番した一意な値を主キーとすることをサロゲートキーといいます。
「年次・クラスID・生徒番号」はナチュラルキーといいます。
基本は全テーブルをサロゲートキーにする方が効率的です。
サロゲートキーを使わない例外
私なりの基準は関係テーブルの場合、他に情報がない場合サロゲートキーは使いません。
例えば
■ 生徒テーブル
- ID
- 年次
- クラスID
- 生徒番号
- 名前
- 生年月日
- ……
■ 科目マスタ
- ID
- 名前
- ……
■ 履修マスタ(サロゲートキーなし)
- 生徒ID
- 科目ID
キーしか情報を持たない履修マスタにはサロゲートキーのIDは付けません。しかし、キー以外の情報が存在するときはサロゲートキーを付けます(付けないこともあるが)。
■ 料理マスタ
- ID
- 名前
- ……
■ 材料マスタ
- ID
- 名前
- ……
■ レシピマスタ
- ID ← 関係テーブルにもIDを付ける
- 料理ID
- 材料ID
- 使用量 ← 付加情報があるときに
他に N-Gram で切ったデータのように全く意味のないデータのときはサロゲートキーは使いません。
この辺は、私のクセ的なもので、将来育つ可能性もあるので付けた方が良いような気もする。しかし、SQLServerの様にクラスタードインデックスを想定しているからなんですけれど、関係マスタはインデックスの塊になるので、主キーは複合キーにしておきたいと思うわけです。最近のマシンなら例外を作らず、無条件にサロゲートキーを付けた方が良いような気もします。
テーブルの命名法
テーブルは ID + 名称で付けます。
桁数はプロジェクトによって違うとは思いますが、
一桁目 テーブルの役割を表すアルファベット
M(マスタ)、T(トランザクション)、S(システム)、W(ワークテーブル)など
二桁目 0〜9(足りないときはアルファベット)の文字、データの大分類
三桁目 0〜9(足りないときはアルファベット)の文字、データの中分類
四桁目 0〜9(足りないときはアルファベット)の文字、データの小分類
例)
物理名 | 論理名 |
---|---|
M000_Foods | 料理マスタ |
M010_Food_info | 料理補助情報マスタ |
M100_Ingredients | 材料マスタ |
M110_Ingredient_info | 材料補助情報マスタ |
M200_Recipes | レシピマスタ |
外部キーはテーブルIDで
例えば、M200_Recipes は
物理名 | 論理名 |
---|---|
ID | ID |
Food_ID | 料理ID |
Ingredient_ID | 材料ID |
Quantity | 使用量 |
としたくなるところですが、
物理名 | 論理名 |
---|---|
ID | ID |
M000_ID | 料理ID |
M100_ID | 材料ID |
Quantity | 使用量 |
とします。
同じテーブルで、複数の料理IDが必要なときは
物理名 | 論理名 |
---|---|
ID | ID |
M000_ID_Main | メイン料理ID |
M000_ID_Option | オプション料理ID |
とします。もちろん、『_ID』を省略するなどの工夫もありかも知れません。
SQLを書くときには、テーブル名のエイリアスにテーブルIDを利用します。
SELECT
M200.M000_ID AS 料理ID
, M000.Name AS 料理名
, M010.Memo AS 料理メモ
, M200.M000_ID AS 材料ID
, M100.Name_ID AS 材料名
, M110.Memo AS 材料メモ
FROM
M200_Recipes M200
INNER JOIN M000_Foods M000
ON M200.M000_ID = M000.ID
LEFT JOIN M010_Food_Info M010
ON M200.M000_ID = M010.M000_ID
INNER JOIN M100_Ingredients M100
ON M200.M100_ID = M100.ID
LEFT JOIN M110_Ingredient_Info M010
ON M200.M100_ID = M110.M100_ID
ナチュラルキーを使っていても、慣れた人なら主なSQLを見ればER図が思い浮かぶでしょうが、サロゲートキーを使った方がより簡単にER図が浮かぶようになります。
さらに、サロゲートキーを使って上の様な命名法を取れば、常に一つのIDで結合でき、その結合すべき相手がテーブル名、フィールド名で分かります。つまり、ER図がなくてもざっくりテーブル一覧を眺めれば、プログラムが書けるようになりますし、いくつかの主なSQLを見れば、頭の中にER図が浮かぶようになります。
少々、大きなDBになっても、何をやっているか見れば分かるというレベルになるし、下手くそなSQLを書くことは難しく、プログラムの質を均一にすることにも役に立ちます。
こっちからやるべきだったか……
まあ、最終的には楽になるのですが、技術者だけではなく、顧客(情報システム部など)からも非常に大きな抵抗がある。大抵の場合、過去からのしがらみと、抵抗と、移行作業の大変さのために諦めることが多いです。
移行作業は、ちょっとしたスクリプトを組めば出来ることですが、7個とか、8個もあるような複合キーのテーブルも存在するのに、資料がいい加減でどうにも出来ないとか……。
他のことでケンカ(苦笑)しないといけないし、移行作業は、ほぼ、私に回ってくるので、私も尻込みをしていたというのが本当の所ですけれど、よく考えるとそういう状況ならどうせ嵌まります(苦笑)。であれば、苦しんでも最終的にきれいになる方を選んだ方が良いでしょう。
次からは、こちらでケンカしようかなと思ったり、思わなかったり……。
どうせ負けるんですけどね(苦笑)
*1: 説明上、M200と補助情報のテーブルを繋ぎましたが、実際には料理マスタと料理補助情報マスタを繋ぐことの方が多いかな。
意味的にはどちらも間違っているとは言いがたい。