MS Access Tips/Sample and VBA and Blog customize etc...

主キーに複数の意味を持たせるな

よく主キーを下記のようにしているテーブル設計をみかけます。

社員番号: TKO010001
[支社コード(3桁)]+[部署コード(2桁)]+[社員番号(4桁)]
売上明細コード: 200910A0001
[売上年月(6桁)]+[部門コード(1桁)]+[枝番(4桁)]
商品コード: 010010001
[大分類(2桁)]+[小分類(3桁)]+[枝番(4桁)]

ようするに、一つのフィールドに複数の項目を連結して格納するものです。

まったく無意味な主キーと比べて人間がみて判断しやすいし、効率的と思ってこのような設計にするのだと思いますが、実は、データベースから見ればデメリットの多い設計になります。

今回はこの点について解説してみます。

難易度:

■■変更があったときに他の部分に影響を及ぼす■

上記の社員番号の場合、社員が異動になって、支社や部署が変わったとき、主キーを変更する必要が出てくる。主キーを変更すると連鎖しているテーブルの外部キーを変更する必要があったりとか何かと面倒です。リレーションシップで連鎖更新をオンにしておけば自動でしてはくれますが、主キーはできるかぎり固定しておいたほうトラブルが少ないでしょう。

売上明細コード、商品コードなども、予想以上に、売上が拡大した、取扱商品が増えたため、枝番が4桁ではたらなくなった、 分類の桁数が足らなくなった等と言うときに主キーの仕様を変更する必要がでてきます。これも同様に面倒なことです。クエリやコントロールの式やVBAなどで、Mid関数などで切り分けていたりするとその部分も変更する必要がでてきます。

■■インデックスが無効になる場合がある■

例えば、各マスターテーブルの主キーが上記のような設計になっていて、売上明細テーブルに、商品コード、社員番号が外部キーとしてあるとします。

そこで、大分類でグループ化して集計したいというとき、

大分類: Left([商品コード],2)
というような演算フィールドでグループ化することになります。これは、以前の記事 でも解説したようにインデックスが無効になります。支社別、部署別で集計したい、売上年月別で集計したいというときも同様です。

■■どのような設計にすべきか■

基本的に一つのフィールドに複数のカテゴリのデータをいれないことです。これは主キー以外のフィールドでも同じです。

上記の社員番号の場合は、支社コードや部門コードは含めずに、入社したら永遠に変わらない固定の番号を付与してこれを主キーとし、支社コード、部門コードは別フィールドにします。

商品コードも、大分類、小分類、枝番 の3つのフィールドに分解して、この3つのフィールドを複数フィールド主キーに設定します。(参照: 複数のフィールドの組み合わせで重複がないようにする) あるいは、主キーにはせずに複数フィールドインデックスを設定するだけにして、主キーは別にオートナンバー型等で作成します。

前者は外部テーブルとリンクするときに3つの結合線が必要になりクエリが複雑になり面倒な部分もあるので、私の場合は後者の設計にする場合が多いです。

売上明細コードのようにトランザクション系のテーブルは、オートナンバー型のフィールドを主キーにするか、数値型のフィールドを主キーにして自動採番する機能をVBAで作り込むのがいいでしょう。売上日、売上場所 などは別フィールドにします。特にトランザクション系のテーブルの主キーにはユニーク性以外の意味を付与しないようにした方がいいでしょう。


拍手する

Leave a reply






Trackbacks

trackback URL
http://hatenachips.blog34.fc2.com/tb.php/201-af909a40
該当の記事は見つかりませんでした。