テーブル設計の理想と現実

こんにちは。SI部の廣田です。

DBに関する本を読んでみると、必ず出てくるのが「正規化」というキーワードです。

特に第3正規化はテーブルの冗長性をなくす意味で、テーブル設計において重要な要素と言えるでしょう。

テーブル1：

テーブル1を参照して下さい。社員IDをPKとするテーブルの所属事務所電話番号は明らかに所属事務所コードに従属しています。所属事務所コードが決まればその電話番号も一意に決まるため、この2項の関係が冗長となっています。

これを第3正規化するとこうなります。

テーブル1-1：

テーブル1-2：

このように、「すべてのカラムはPKにだけ従属するべきだ」という考え方があります。所属事務所の電話番号が変更になったとき、うっかりテーブル1のレコードのうち一部のレコードを更新し忘れたら。あるいはあるひとつの事務所に所属する社員がいなくなったとき、その事務所の電話番号の情報が失われるなどの問題が発生するからです。

さて、次のテーブルはどうでしょうか。

テーブル2-1：

テーブル2-2：

テーブル2-3：

ある店舗での売り上げについて、伝票単位での商品内訳とその価格、支払内訳の情報をそれぞれ持つテーブル群です。

現金の代わりに1ポイント＝1円の価値を持つポイントで支払うことも、現金とポイントを合わせて支払うことも可能です。

表を見ればわかるように、利用ポイント数と利用ポイント額は常に同じ値となりますが、ではこの2つのカラム間は冗長でしょうか。

一応、このどちらかがなくなっても今のところ困ることはありません。

テーブル2-3’：

ところがポイント価値が変更されたらどうでしょうか。

ある日を境に1ポイント＝0.5円となり、またある日からは2円となる、などとなってしまうと、この利用ポイントがいくらの価値に相当したのかわからなくなってしまいます。

こういった問題に対応するために、「変わるもの」はすべて履歴を取るという方法があります。

価値の変更履歴を持つマスタを別に作るわけです。

テーブル2-4：

このテーブルさえあれば、利用ポイント数と販売日付から利用ポイント額は算出されます。

一応これで解決ではあるのですが、さて、本当にこれでよいのでしょうか。

このテーブル2-3’を参照するたびに、1レコードごとにその利用ポイント額の計算処理が必要になります。別のテーブル2-4と結合までして得られたその結果はレコードごとに常に同じ値です。何度計算しても結果が一緒なら、その結果も含めてはじめから格納してしまえばよいのではないでしょうか？最初のテーブル2-3のように、冗長ではあっても計算結果が参照出来るほうが利便性が高いと言えるのではないでしょうか。

テーブル3-1：

ある店舗では、支払いに日本円と米ドルの両方が利用可能となっています。

当然一日ごとの為替レートの変動を履歴として別テーブルに持ってはいるのですが、その結果も含めて上のテーブルに格納するべきではないでしょうか。

テーブル3-2：

このような計算を伴うものに関しては、その計算ルールが変更になると従属性が失われてしまいます。内税の計算は消費税率の変更によって、割り算によって発生した端数の扱いは企業の規約の変更によって、ある日を境に代わる可能性があります。「AはBによって一意に決まる」という想定が、実際に運用が始まってからそうでなくなってしまうという可能性もあるのです。

実際の業務においてテーブル設計をする場合、大切なのは「どの値が変更する可能性があるか？」という事です。

最初に十分にヒヤリングを行っておくことが大事ですが、とは言え仕様は変更されるもの。あのときはこう言ったけどもやっぱりああしてくれ、などの要望が挙がることは珍しいことではありません。

数値に関わることに限らず、すべてのコードは変更される可能性があるのであれば、はじめからすべての要素にはID振っておくという考え方があります。

テーブル設計において、IDとコードは混同されがちです。以下IDと言ったら変更されないもの、コードは変更されるものと扱われるとします。

テーブル4-1：

このテーブル4-1の伝票IDの数値を見ると、なんとなく最初の8桁は日付だなという事に気付くでしょう。実際この伝票IDの採番ルールは、日付8桁＋店舗コード4桁＋レジ番号4桁＋連番4桁というものになっていました。いわば4つのカラムを持っているわけですが、店舗コードやレジ番号などは変更される可能性があります。実際変更された場合、さてこの伝票IDまで変更するべきでしょうか。

PKであるIDを一度採番して、あとから変更すると、このIDを参照している別のテーブルにも影響が発生します。基本的にPKに手は入れたくありません。

こういう問題を回避するために、（たとえ冗長と思われようとも）IDとコードを別に設けるという方法があります。

テーブル4-2：

PKは伝票IDとしますが、その値は単なる連番です。先ほどまでの伝票IDは伝票コードと名前を変えました。こちらは後から変更してもまったく構いません。

別のテーブルが参照するのはこの伝票IDの方。これならレジ番号があとから変わってもそのテーブルに影響はありません。

何も変更されることがなければ「ひとつのレコードを一意に決定できるカラムがふたつある」ことになるわけですが、別に構わないと思ってしまえばいいのです。

机上で学ぶことと、現場で求められる事のギャップを感じることはよくあります。理想よりもビジネス上のルールを優先した、現実的な設計にも対応できた方がいいでしょう。

最後まで読んでいただき、ありがとうございました。