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

一対一関係のテーブル設計

一対一の関係のデータは一つのテーブルにまとめるというのが、データベース設計のセオリーです。ですので、特別な事情が無い限りは一対一関係のテーブルを作るメリットはないです。ただ、絶対に一対一の設計はNGというわけでもありません。

一対一の関係が必要な特別な事情の例と、その場合の設計法や注意する点について、今回は取り上げます。

一対一関係のテーブル

難易度:

一対一の関係が必要な特別な事情

【フィールド数が、制限の255を超えしまう場合】
正規化されたテーブルなら超えることはめったにありません。 まずは、正規化で分割できないか検討すべきです。 ただ、絶対無いとはいいきれません。

【データ管理の都合で分ける必用がある場合。】
例えば、社員情報データで、社員番号、氏名、部署、等はだれでもアクセスできるが、給与、査定結果 などは、権限を持ったものだけアクセスできる、というような管理をしたい場合などです。
社員機密情報のフォーム

【特定のデータが一部のレコードのみに必用な場合】
例えば、顧客会社情報データで、株式会社/有限会社では、資本金、などのデータが必用であるが、個人会社には必要ない場合は、そのデータだけ別テーブルにするという設計もあり得ます。ただし、一つのテーブルで、必要ないフィールドは未入力にしておくという設計でもデメリットは少ないです。

一対一関係のテーブルの設計

まず、一対一の関係といっても、まったく対等ではなく、主従の関係があります。一対一の関係は、結局は、一対多の関係の一つのバリエーションで、多側のデータが一つしかないものといえます。そこで、主になるデータが一側、従になるデータが多側というような設計にします。

社員情報のデータを例にして説明しますと、

■社員
・社員番号  主キー
・氏名
・部署
・住所
・入社年月日
・生年月日
 ・・・・

■社員機密情報
・社員番号  主キー
・給与額
・査定結果

というようなフィールド構成になります。「社員」テーブルが主、「社員機密情報」が従となります。
一対一の場合は、どちらも主キーが同じフィールドになり、主キー同士でリレーションすることになります。両者のデータ型は同じにする必用があります。ただし、オートナンバー型の場合は、主となるテーブルのほうをオートナンバー型、従となるテーブルの方を長整数型とします。

従となるテーブル(社員機密情報)の主キーのフィールドプロパティの「値要求」は「いいえ」、「規定値」は設定しないようにします。

リレーションシップは設定した方がいいでしょう。主キー(社員番号)同士で結合して、参照整合性、連鎖更新、連鎖削除 にチェックを入れておきます。
リレーションシップ設定

入力用クエリの設計

設計はこれでOKですが、運用面で、片方のテーブルにレコードを追加したら、他方のテーブルにも同じ主キー(社員番号)のレコードを自動で追加したいとい希望が出てくると思います。これを実現するには、フォームで新規レコードを追加したタイミングでVBAで追加と考えるかも知れませんが、クエリの機能を使うことで簡単に実現できます。

上の社員情報の例ですと、下記のような外部結合のクエリを作成します。
クエリ「Q_社員情報」

ポイントは、主キーフィールド(社員番号)は主となるテーブル(社員)の方を表示させて、従の方は表示させないと言うことです。

このクエリで、一つのテーブルと同じように入力出来ます。
社員番号を入力してから、従の方のデータを入力すると、自動的に従のテーブルの社員番号に主の社員番号が入力されます。
主の方の社員番号を更新すると、連鎖更新の機能で、従の方も更新されます。
レコードを削除すれば、連鎖削除の機能で、両方のテーブルから削除されます。

このページ上部のフォームはこのクエリをレコードソースとしてますが、ユーザーは一対一ということを意識することなく一つのテーブルに入力してる感覚で操作できます。

下記リンクからサンプル MDB をダウンロードできます。
tbl1To1Relation_07.zip (Access 2007-2013 形式 - 31kb)
tbl1To1Relation.zip (Access 2002-2003 形式 - 19kb)
tbl1To1Relation2k.zip (Access 2000 形式 - 18kb)


拍手する

2 Comments

まらいや says..."はじめまして!1:1テーブルについて。"

 大変詳しいアクセスのプログラム、参考にさせてもらいます。
この1VS1のテーブルについて、一つのバリエーションとして現在取り扱っています。

 部門システムと基幹システムを併合して使うような場合です。 部品マスタのメインは
基幹システムに有りますが、全くもって基幹システムにある部品マスタでは情報不足です。
 そのため、部門システム側でも等価の情報を、貴殿様がお書きになっているように主キー
同士で結合させ、ビューにして利用していくものです。
 全然スマートじゃないですが、仕方無い感じですね。 「部品マスタは併合して利用して
行くんだ!」という無茶苦茶な要求がこうなるのでしょうね。苦笑
 貴殿様がお書きのように、同期問題が大変でした。

2012.09.01 02:04 | URL | #Uh60rX9Y [edit]
hatena says..."re:はじめまして!1:1テーブルについて。"

> 全然スマートじゃないですが、仕方無い感じですね。

実態が正規化されてないので、なかなか理論通りの設計にはいかないものですね。

2012.09.01 11:25 | URL | #5uE6dEgY [edit]

Leave a reply






Trackbacks

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