複数テーブルに同一の外部キーやカラムがある場合

例えば、ホテルテーブル、レストランテーブル、映画館テーブルなど施設別のテーブルが多数あり、
そのそれぞれが、所在エリアを示すエリアＩＤを持つ場合、テーブル１つずつにエリアＩＤを外部キーとして持たせるか、施設テーブルという抽象的なテーブルを作り、そこにエリアＩＤを持たせ、具体的なホテルテーブルなどは施設テーブルと１対１で結びつけるべきか、迷っていますが、どちらがいいでしょうか？
エリアＩＤ以外にも共通のカラムがあります（名称、住所など）。
今後、具体的な施設テーブルは増える可能性があります（遊園地テーブルなど）。
また、エリアＩＤ以外にも共通の外部キーも増える可能性があります（所有会社ＩＤなど）。
そう考えるとテーブル１つずつにリレーションを付けるとＥＲ図が線だらけになる気がします。
しかし抽象テーブルを作ると、データを取ってくるたびに毎回ＪＯＩNしないといけないのが気になります。
ご意見お願いします。

No.1 回答者： chie65535 回答日時： 2011/05/31 14:09

データの「持たせ方」で決めましょう。

例えば、施設１つ１つに固有な「ユニークＩＤ」を持っている場合「ＩＤの１～２桁目を施設ＩＤと同一に、３～５桁目をエリアＩＤと同一にしておく」と言う持ち方をした場合は、以下のようにした方が楽です。

種別テーブル
01:ホテル
02:レストラン
03:映画館

エリアテーブル
001:ベイエリア
002:駅前
003:タウンパーク

施設テーブル
010010001:ベイエリアホテル
010020001:駅前ホテル第一
010020002:駅前ホテル第二
010020003:駅前ホテル第二別館
010030001:ホテルタウンパーク
020010001:ベイエリアホテル１Ｆラウンジ「カトエラ」
020010002:ベイエリアホテル15Ｆバー「BAY NIGHT」
020010003:ベイエリアホテル２Ｆ和食處「祇園」
030020001:駅ビルシネマタウン
030030001:シネマタウン・タウンパーク館

ユニークIDを切り出せば、種別やエリアを別テーブルから参照できます。

ユニークIDから各コードを切り出すのが面倒なら、以下のように、ユニークIDの代わりに「種別コード」「エリアコード」「ID番号」の３つを持たせれば良いです。

施設テーブル
01:001:0001:ベイエリアホテル
01:002:0001:駅前ホテル第一
01:002:0002:駅前ホテル第二
01:002:0003:駅前ホテル第二別館
01:003:0001:ホテルタウンパーク
02:001:0001:ベイエリアホテル１Ｆラウンジ「カトエラ」
02:001:0002:ベイエリアホテル15Ｆバー「BAY NIGHT」
02:001:0003:ベイエリアホテル２Ｆ和食處「祇園」
03:002:0001:駅ビルシネマタウン
03:003:0001:シネマタウン・タウンパーク館

ここで注目して欲しいのは、施設テーブルが、ホテルもレストランも映画館も、すべて１つのテーブルに入っている、と言う事です。

こうする事で、「名称」や「住所」など、全施設に共通な項目は、このテーブル１つで済む、と言う事と、施設の種類やエリアが増えても、テーブルそのものを増やさなくて良い、と言う事。

で「厨房責任者」や「映写技師」など「レストランのみに要る項目」や「映画館のみに要る項目」は「レストラン付随テーブル」や「映画館付随テーブル」として別テーブルにしておき、固有IDやコードでJOINして呼び出します。

レストラン付随テーブル（支配人、厨房責任者、衛生管理責任者）
02:001:0001:小泉純一郎:安倍晋三:福田康夫
02:001:0002:麻生太郎:鳩山由紀夫:菅直人
02:001:0003:橋本龍太郎:小渕恵三:森喜朗

映画館付随テーブル（館長、映写技師）
03:002:0001:田中角榮:竹下登
03:003:0001:佐藤榮作:小沢一郎

データベースを設計する上で、最も重要な事は「何かが増えても、テーブルそのものを増やさなくても良いように設計する」と言う事。

後から遊園地が増えても、遊園地テーブルを増やすような事はせず（遊園地固有のデータを入れておく「遊園地付随テーブル」だけは、増やして構わないが）、種別テーブルの中に遊園地レコードを追加する、施設テーブルに遊園地データを追加する、など「レコードを追加するだけで、どんどん新規の種別、新規のエリア、新規の施設が増やせるようにするのです。

以下蛇足なクイズ：サンプルデータの氏名の中で、仲間ハズレは誰でしょう？

No.2 ベストアンサー 回答者： chie65535 回答日時： 2011/05/31 14:30

長くなったので分割して続き。

で、どうして「テーブルを増やさないようにするか」の理由は「テーブルを増やすと、付随したクエリも増えるし、ユーザーインターフェース部分も新規に追加しないとならないから」です。

新しい種別が増えるごとに新テーブルを増やしていたら、その新規テーブルにアクセスするユーザーインターフェースもすべて新規作成になるし、入力や閲覧や検索や印刷ルーチンも、全部、新規作成になります。

そういう作り方をせず「テーブルは全部共通で１つ」にすれば、ユーザーインターフェースは１つで済み、ホテルだけ扱いたい場合は「フィルターを設定してホテルだけ抽出」とかが可能だし、駅前エリアの全施設を出したい場合は「フィルターを設定して駅前だけ抽出」とかも可能です。

もちろん、フィルターを設定しないでおけば「すべての施設を全部閲覧」とかが可能です。

このように「テーブルを１つにする」ことで「複数の種類、複数の種別を、一括で操作できるようになる」と言う利点もあります。

検索を行う場合も「どこかの施設に居る、役職が何か判らない、鳩山一郎さんを探す」と言う場合、テーブルがバラバラだと、全部のテーブルを検索しないとなりません。

その場合、テーブルが増えるたびに、増えたテーブルも探しに行くよう、検索ルーチンに手を加えないとならなくなります。

なので「できるだけ、メインのデータが入るテーブルは１つで済ます」のです。

ですので「種別の数だけテーブルを作る」とか「エリアの数だけテーブルを作る」と言う作り方は、やってはいけません。

この回答へのお礼

大変詳しいご説明をしていただき、ありがとうございました。
仕様が拡張されても、なるべくテーブルを増やさずに済むように設計するという方針は、非常に納得できました。

お礼日時：2011/06/03 12:36