マスタテーブルを作成するときに、何に気をつければいいでしょうか？
私も昔、情報が見つからなくて感覚で作っていました。
後で問題点が見つかって作り直した経験は何度もあります。
その経験を元に注意点についてまとめます。
この記事を見れば大きな手直しはなくなるでしょう。
ここではマスタ設計時の次の３点の注意点を見ていきます。
「そもそもマスタって何？」
という方はマスタテーブルとはいったい何か？の記事をご参照ください。
情報システム部員で、実務に役に立つ知識を中心に解説しています。
著書「Power BIの教科書」でレポートツールに関して出版。
レポートのため何十ものシステムのデータベースを見ているので、テーブルの実装パターンに詳しいです。
マスタテーブルは次の３つの特徴があります。
これから具体的に設計で注意する点を確認していきます。
注意する理由はこの３つから発生しています。
ですので、この３つを覚えて意識するのがマスタの設計のコツです。
結論からいうと、主キーには連番を使います。
主キーの値に意味をもたせることは厳禁です。
商品テーブルの例で見てみます。
商品名には商品コードが多くの場合あります。
例えばPanasonicのドラム缶洗濯機ではNA-LX129ALです。
| 商品コード | 商品名 |
| NA-LX129AL | ななめドラム洗濯乾燥機 NA-LX129AL |
| NA-LX127AL | ななめドラム洗濯乾燥機 NA-LX127AL |
| NA-VG2600L | ななめドラム洗濯乾燥機 NA-VG2600L |
この商品コードは主キーで使えますでしょうか？
商品コードは重複しないので主キーの条件は満たしています。
ですがこの商品コードを主キーにすることはおすすめできません。
理由は商品コードに意味があるからです。
NA-LX129ALのLXはシリース名、12は洗濯容量といったところでしょうか？
もしLXのシリーズ名が他社と重複するので変えると重役会議で決まったらどうなるでしょうか？
開発が進んで洗濯容量が13kgに拡大できたら、商品コードは変えるべきでしょうか？
このように外部要因でコードが変わる可能性場合は、マスタの主キーにふさわしくありません。
ここでマスタの特徴の「トランザクションを入力する前に登録が必要」を思い出してください。
商品コードは最初に登録します。
システムに入力するときに商品コードがまだ決まっていないということもありえます。
主キーは連番にして、品目コードという項目を持つように設計します。
マスタの主キーには、外部の人の意見で変わるような値は使わないようにしましょう。
取引先の会社の名前が４月１日付けで変更するという連絡がきました。
その時どのようにマスタを変更すればいいでしょうか？
連絡を受けた3月1日変更すると、3月に発注した注文書の名前が変わってしまいます。
かといって、4月1日の朝早く取引先名の名前を変更するのも大変です。
このようときのパターンは３つあります。
それでは次にそれぞれの特徴を見ていきます。
取引先、品目マスタ、社員マスタなど多くの場合は何もしないでそのまま持ちます。
では、取引先の会社の名前が４月１日付けで変わる場合はどうするのでしょうか？
それはトランザクションテーブルの設計で対応します。
トランザクションテーブルに取引先の名前の情報をもたせます。
「えっ、正規化で取引先名は別に持つように習ったよ。」
と思った方もいるでしょう。
契約情報などあとからデータが変わると問題になるデータは、トランザクションを作成した時点でマスタの情報をコピーしてもたせます。
取引先名が変わったら発注前の注文書を確認して、必要に応じて変えます。
ですので、変更ルールさえ決まっていれば大きな問題になることはありません。
マスタのテーブルに、有効日と失効日をもつ方法があります。
社員の所属部署のテーブルでは次の表のようにデータを持ちます。
| 社員番号 | 有効日 | 失効日 | 部署 |
|---|---|---|---|
| 001 | 2020-04-01 | 2021-08-31 | 情報システム |
| 001 | 2021-08-31 | 9999-12-31 | 人事 |
今日時点のデータを元に対象のデータを取得します。
今日が2021-10-01でしたら、２行目の人事が所属部署です。
この方法のメリットは将来の変更を事前に登録できることです。
部署は一斉に変わるので事前に登録できると便利です。
また過去の履歴を確認できるメリットもあります。
対象の社員がどの部署を経験してきたかデータとして残ります。
有効日と失効日で多いのは、組織テーブルや部品表です。
その他のマスタではあまり使っているのを見たことがありません。
バージョン（版数）を使う代表例は部品表です。
AAAの1版を使う、2版を使うといったように使うときに版までいれた主キーを使います。
バージョン（版数)は部品表以外ではあまり見たことはありません。
| 親部品 | 小部品 | 版数 |
|---|---|---|
| AAA | B123 | 1 |
| AAA | C456 | 2 |
マスタは一度データを登録したら削除するのは難しいです。
そのため、データ件数が大きくなりすぎないように注意して設計します。
製品の原価の例で見てみます。
次のテーブルのように品目マスタを持つと原価費目の種類増えるたびに項目を追加しないといけません。
| 品目コード | 直接材料費 | 間接材料費 | 直接労務費 |
|---|---|---|---|
| A-100 | 800 | 400 | 300 |
このように原価費目を項目として持つと、あとから費目を自由に追加できるのですっきりします。
| 品目コード | 原価費目 | 原価 |
|---|---|---|
| A-100 | 直接材料費 | 800 |
| A-100 | 間接材料費 | 400 |
| A-100 | 直接労務費 | 400 |
品目マスタの件数が少ない場合は、テーブルの持ち方はどちらでもかまいません。
ではこの品目マスタの件数が100万件になったと考えたときはどうでしょうか？
もし原価費目が10個あったら1,000万件となってしまいます。
この場合1つ目のデータの持ち方のほうがよくなります。
マスタテーブルの件数が増えても、登録処理のパフォーマンスはそれほど影響はでません。
登録処理は1件1件処理するからです。
影響が大きいのはレポートです。
レポートでは全体をまとめて参照するので、マスタの件数が多いとパフォーマンスに影響します。
それが結果的に登録処理のパフォーマンスに悪影響をもたらします。
マスタテーブルは一度登録すると削除が難しいので、レコード数が多くなりすぎないように設計しましょう。
今回はマスタテーブルの設計で、注意する3点を確認しました。
それではこの３点に注意してマスタテーブルの設計をしていきましょう。