明細テーブルの主キーの持ち方

明細テーブルの主キーの持ち方

   少しアプリケーションの設計に話を移しましょう。まずは明細テーブルの主キーの持ち方です。

   図3に示すNorthwindでは、受注明細テーブルの主キーは、「受注番号+商品番号」の複合キーになっています。


   これでもちゃんと一意になるのでこのままでもよさそうですが、通常のアプリケーションではこのような主キーの持ち方はしません。受注明細は「受注伝票」の明細欄に相当しますので、その並び順が重要です。つまり明細の登録順に並ぶべきであって、再表示したら商品コード順に切り替わるような作りでは困るのです。

   図4では行番号という列を追加して、「受注番号+行番号」を主キーにしています。


   明細行の登録順に行番号が1ずつ繰り上がって一意の値となります。この形式ならば、再表示しても行番号順にデータが並びます。また、もう1つの利点として同じ商品コードを複数明細に登録することもできます。

   例えば、ある商品が箱（入数12）と個（入数1）という2種類の単位を持っていたとすると、14個の受注を入力したい場合に1箱と2個というように複数明細に分けて登録するかもしれないためです。

   図4のような設計は非常に一般的だと思いますが、筆者はこれではまだ不満があります。それは、後から行挿入や行削除をした場合に主キーが変わってしまうからです。

   ビジネスでは、伝票の変更や修正が当然発生します。例えば、1行目にスープ缶を1箱、2行目にカレーライス2袋の受注伝票があったとします。お客様から変更要望があってスープ缶をバラで2個追加する場合、通常はこの注文を2行目に追加し、カレーライスは3行目に移動させます（スープ缶が並んでいた方がわかりやすいためです）。

   ちょっと気の利いたアプリケーションでは、このような行挿入機能を用意しています。しかしエンティティが図4の構造だと、その際に主キーの変更が生じてしまいます。つまりカレー袋のデータは、主キーの一部となっている行番号が2から3に変更になるのです。

   RDBMSでは主キーをできるだけ変更しないのがよい設計とされています。それは、外部との整合性が取れなくなる恐れがあるからです。例えば、図5のように受注明細データを外部の倉庫業者へ出力したとしましょう。もしもその後で、明細データの主キーが変更になったとすると、外部では2行目はカレー袋だと信じて処理をしているのに、いつの間にかスープ缶に変わってしまっていることになりかねません。


   そこで筆者は、図6のように行番号の他に行表示番号という列を用意して、行挿入・行削除に備える設計にしています。


   基本的に、行番号と行表示番号は登録順に自動採番されます。そして後から行挿入などで表示順を変更する場合は、主キーである行番号はそのままにして行表示番号だけ変更するのです。このような構造にすれば、「主キーを変えない」「表示順は変更できる」という両方のニーズを満たすことができるのです。

最後に

   今回は、「制約を全部使うか?」と「Unicodeを採用するか?」の2点を設計課題に取り上げました。また、後半はアプリケーションの設計に話題を移し、第1弾として「明細テーブルの設計」について筆者の考えをご紹介しました。

   本連載は当初4回の予定だったのですが、まだまだお伝えしておきたい設計ポイントがたくさんありますので、もう1回延長しようと思います。最後までお付き合いいただければ幸いです。