テーブル設計の指針(備忘録)

最近、DB設計やデータモデリング等のDB周辺について集中して触れることがあったのでテーブル設計における指針を備忘録として書いておきます。

サロゲートキーとナチュラルキー
システムテーブルの使い方
セットの応用
インデックスについて
1. 　　　関連するエントリ

サロゲートキーとナチュラルキー

主キーの設定時に大きく2つの設計指針があります。

1つは顧客マスタにおける顧客ID、商品マスタにおける商品番号、また発注テーブルにおける発注番号等、ビジネス(業務)上で意味を持ち、自然に一意となる値を主キーに設定する設計指針です。
これを「ナチュラルキー（natural key）」と呼びます。

もう1つは、システム内部でレコードを一意とする為に採番(その多くは自動採番)する方法です。
この方法で取得した主キーを「サロゲートキー（surrogate key）」とか「人工キー（artificial key）」と呼びます。
※参考：「いざRuby on Railsでプロトタイピング」

RubyOnRailsで使用するActiveRecordではサロゲートキーをある意味前提になっています(そのパフォーマンスを最大限に発揮できます)。

テーブル設計上、どっちが良いか…ですが、私はお客様の要望が無ければサロゲートキーを採用します。

ナチュラルキーの代表的なものである顧客IDや社員番号、商品番号等は先程も書いたようにお客様(直接でなく間接的な意味も含め)が決めたものです。
そしてその決め方、不変である保証はいつ(お客様の都合により)変わるともしれないのです。

そういう外部(ここでいうお客様)に決定要因を持たれていることは、設計において(またプロジェクト全体の視点からも)リスクが高くなります。「自分の預かり知らぬところ」「手が出せないどうしようもないところ」だからです。

一方、サロゲートキー方式の場合、主キーはシステムの表面上に現れないので、仕様変更にも比較的柔軟に対応できます。
※参考：「システムの寿命はコードで決まる！」

システムテーブルの使い方

会社名や運用日付、伝票Noカウンター等を保持する方法にシステムテーブルを作成して保持する設計指針があります。
通常、このシステムテーブルは1レコードのみでレコードの増減はありえないのですが、この時、ちょっと拡張性を考えて、視野を広く持てば複数レコードとなっても良いように主キーを設定します。

将来的に同一システム上で複数部署、会社等に横展開しやすくする為です。

※参考：「業務システムモデリング練習帳」

セットの応用

テーブル設計には直接関係ないのですが、業務分析をうまくテーブル設計に反映させましょうという内容です。

例えば薬や部品等、「Aを注文する際には必ずBもCも注文するよ」というセット(組合せ)があることが多かったりします。で、普通のシステムの注文機能だとA,B,Cは3回注文する品を選ぶ必要があります。

Aを選べばB,Cにもデフォルトの選択がされていれば現場は助かると思いませんか？

こういう内容は普通にヒアリングしているだけでは出てこない内容です(おそらくそもそも要求事項として上がって来ないでしょう)。
ただ、使用するユーザ(=現場)にとっては面倒なことだったりして、運用テストの段階で仕様追加という名前で出てくることでもあります。そしてこの要望を実現しようとすれば、その組合せ情報を保持する関連テーブルが必要だったりして、すぐには対応できません。
ですので、業務分析、ヒヤリング時に、この視点でも考えてみて、適用できそうだったらこちらから提案できれば良いと思います。