SQLer 生島勘富 のブログ

RDB・SQLの話題を中心に情報発信をしています。

命名法 - 主キーはサロゲートキーで、テーブル名はナチュラルキーで(笑)

 完全に新規の案件というのは本当に少ないので、実践できることはほぼない理想論ですが、私の理想とするテーブル構造と命名法です。

まずは、サロゲートキーについて

 サロゲートキーというのは業務上意味のないキーのことです。

 例えば、生徒テーブルは、年次・クラス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 は

物理名論理名
IDID
Food_ID料理ID
Ingredient_ID材料ID
Quantity使用量

 としたくなるところですが、

物理名論理名
IDID
M000_ID料理ID
M100_ID材料ID
Quantity使用量

 とします。

 同じテーブルで、複数の料理IDが必要なときは

物理名論理名
IDID
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


*1

 ナチュラルキーを使っていても、慣れた人なら主なSQLを見ればER図が思い浮かぶでしょうが、サロゲートキーを使った方がより簡単にER図が浮かぶようになります。
 さらに、サロゲートキーを使って上の様な命名法を取れば、常に一つのIDで結合でき、その結合すべき相手がテーブル名、フィールド名で分かります。つまり、ER図がなくてもざっくりテーブル一覧を眺めれば、プログラムが書けるようになりますし、いくつかの主なSQLを見れば、頭の中にER図が浮かぶようになります。

 少々、大きなDBになっても、何をやっているか見れば分かるというレベルになるし、下手くそなSQLを書くことは難しく、プログラムの質を均一にすることにも役に立ちます。

こっちからやるべきだったか……

 まあ、最終的には楽になるのですが、技術者だけではなく、顧客(情報システム部など)からも非常に大きな抵抗がある。大抵の場合、過去からのしがらみと、抵抗と、移行作業の大変さのために諦めることが多いです。

 移行作業は、ちょっとしたスクリプトを組めば出来ることですが、7個とか、8個もあるような複合キーのテーブルも存在するのに、資料がいい加減でどうにも出来ないとか……。

 他のことでケンカ(苦笑)しないといけないし、移行作業は、ほぼ、私に回ってくるので、私も尻込みをしていたというのが本当の所ですけれど、よく考えるとそういう状況ならどうせ嵌まります(苦笑)。であれば、苦しんでも最終的にきれいになる方を選んだ方が良いでしょう。

 次からは、こちらでケンカしようかなと思ったり、思わなかったり……。
 どうせ負けるんですけどね(苦笑)

*1: 説明上、M200と補助情報のテーブルを繋ぎましたが、実際には料理マスタと料理補助情報マスタを繋ぐことの方が多いかな。
 意味的にはどちらも間違っているとは言いがたい。