複数テーブルに同一の外部キーやカラムがある場合
- 複数テーブルに同一の外部キーやカラムがある場合、どちらの方法が最適か悩んでいます。
- 各テーブルごとにエリアIDを持たせるか、抽象的な施設テーブルを作り関連付けるかを検討しています。
- データの取得の手間やER図の複雑さなどを考慮しながら意見をいただきたいです。
- ベストアンサー
複数テーブルに同一の外部キーやカラムがある場合
例えば、ホテルテーブル、レストランテーブル、映画館テーブルなど施設別のテーブルが多数あり、 そのそれぞれが、所在エリアを示すエリアＩＤを持つ場合、テーブル１つずつにエリアＩＤを外部キーとして持たせるか、施設テーブルという抽象的なテーブルを作り、そこにエリアＩＤを持たせ、具体的なホテルテーブルなどは施設テーブルと１対１で結びつけるべきか、迷っていますが、どちらがいいでしょうか？ エリアＩＤ以外にも共通のカラムがあります（名称、住所など）。 今後、具体的な施設テーブルは増える可能性があります（遊園地テーブルなど）。 また、エリアＩＤ以外にも共通の外部キーも増える可能性があります（所有会社ＩＤなど）。 そう考えるとテーブル１つずつにリレーションを付けるとＥＲ図が線だらけになる気がします。 しかし抽象テーブルを作ると、データを取ってくるたびに毎回ＪＯＩNしないといけないのが気になります。 ご意見お願いします。
- dalianse
- お礼率25% (24/94)
- その他(データベース)
- 回答数2
- ありがとう数1
- みんなの回答 （2）
- 専門家の回答
質問者が選んだベストアンサー
長くなったので分割して続き。 で、どうして「テーブルを増やさないようにするか」の理由は「テーブルを増やすと、付随したクエリも増えるし、ユーザーインターフェース部分も新規に追加しないとならないから」です。 新しい種別が増えるごとに新テーブルを増やしていたら、その新規テーブルにアクセスするユーザーインターフェースもすべて新規作成になるし、入力や閲覧や検索や印刷ルーチンも、全部、新規作成になります。 そういう作り方をせず「テーブルは全部共通で１つ」にすれば、ユーザーインターフェースは１つで済み、ホテルだけ扱いたい場合は「フィルターを設定してホテルだけ抽出」とかが可能だし、駅前エリアの全施設を出したい場合は「フィルターを設定して駅前だけ抽出」とかも可能です。 もちろん、フィルターを設定しないでおけば「すべての施設を全部閲覧」とかが可能です。 このように「テーブルを１つにする」ことで「複数の種類、複数の種別を、一括で操作できるようになる」と言う利点もあります。 検索を行う場合も「どこかの施設に居る、役職が何か判らない、鳩山一郎さんを探す」と言う場合、テーブルがバラバラだと、全部のテーブルを検索しないとなりません。 その場合、テーブルが増えるたびに、増えたテーブルも探しに行くよう、検索ルーチンに手を加えないとならなくなります。 なので「できるだけ、メインのデータが入るテーブルは１つで済ます」のです。 ですので「種別の数だけテーブルを作る」とか「エリアの数だけテーブルを作る」と言う作り方は、やってはいけません。
その他の回答 (1)
- chie65535
- ベストアンサー率43% (8508/19344)
データの「持たせ方」で決めましょう。 例えば、施設１つ１つに固有な「ユニークＩＤ」を持っている場合「ＩＤの１～２桁目を施設ＩＤと同一に、３～５桁目をエリアＩＤと同一にしておく」と言う持ち方をした場合は、以下のようにした方が楽です。 種別テーブル 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:佐藤榮作:小沢一郎 データベースを設計する上で、最も重要な事は「何かが増えても、テーブルそのものを増やさなくても良いように設計する」と言う事。 後から遊園地が増えても、遊園地テーブルを増やすような事はせず（遊園地固有のデータを入れておく「遊園地付随テーブル」だけは、増やして構わないが）、種別テーブルの中に遊園地レコードを追加する、施設テーブルに遊園地データを追加する、など「レコードを追加するだけで、どんどん新規の種別、新規のエリア、新規の施設が増やせるようにするのです。 以下蛇足なクイズ：サンプルデータの氏名の中で、仲間ハズレは誰でしょう？
関連するQ&A
- リレーションシップと外部キー制約について
■最終的にやりたいこと ・なるべくコード(SELECT文など)を見ずに、「DB」「テーブル定義者」「ER図」等からテーブル間の関係性を把握したい ■具体例 ・投稿一覧。「userテーブル」「postテーブル」 ・「postテーブル」の「user_id」カラムは、「userテーブル」の「id」カラムに対応 ※簡易な場合はある程度想像は付くのですが、ちょっと複雑な構成になると途端に苦労するので、何か良い方法はないかと思い、質問しました ■質問 ◆「リレーションシップを組む」際、「外部キー制約」はかけるのでしょうか？ 例えば、上記「投稿一覧」DBを構築する際では、どうするのでしょうか？ 1.普通、「外部キー制約」をかける 2.普通、「外部キー制約」をかけない 3.どちらでも良い ◆「外部キー制約」は何の為にかけるのでしょうか？ ・「SELECT＆JOIN」でデータ取得出来るのであれば、「外部キー制約」と「リレーションシップ構築」に関係性はないと思うのですが、そういう認識で合っているでしょうか？ ・参照先データが削除されたら整合性がとれなくなる場合のみかけるものでしょうか？ ◆「リレーションシップを確認」する目的で、「外部キー制約」をかけても良いのでしょうか？ ・「データ削除の整合性」ではなく、「リレーションシップを確認」する目的で外部キー制約」をかけても良いのでしょうか？ ◆「外部キー制約」以外に、「リレーションシップを確認」する方法はあるのでしょうか？ ・コード(SELECT文など)を見ずに、テーブル間の「リレーションシップを確認」する方法としては、「外部キー制約」以外に何かあるのでしょうか？ ・そもそも、「外部キー制約確認」＝「リレーションシップ確認」という考えは正しいのでしょうか？
- ベストアンサー
- MySQL
- 外部キーだけのテーブル（主キーがない？）
データベースのテーブルについておたずねします． 主キーがなくて，そのかわりに外部キー(と主キー以外の列)しか持たない テーブルも可能だと聞きました． テーブルには主キーが必ずあるものだと思っていましたが， どのような使いかたをするのでしょうか． どうやら，最初からデータがあるわけではなく， 追加されるタイミングがわからないデータを格納する場合に作っておく， ということらしいのですが，なんのことかよくわかりません． データベース関連書籍をいくつか調べましたが， 主キーのないテーブルの説明などは見当たりません． また，この悩ましい問題を与えてくれた知り合いに訊ねましたが， テーブル構成などの具体的なことは， 企業内のことなので，教えてもらえませんでした． 何か具体的な例を交えながらご説明いただければと思います．
- ベストアンサー
- その他(データベース)
- 共通のカラムを持つテーブルの外部キーの設定
MySQLバージョン4.1.16を使用しています。 ２つのテーブル「tbl1」と「tbl2」があり、 それぞれ共通のカラムを別のテーブル「common_tbl」に設定（正規化）し そのcommon_idを「tbl1」と「tbl2」が参照します。 例えば、「tbl2」にデータをinsertする場合に、 まず「common_tbl」からinsertして、そのinsert_idを取得（利用）して 「tbl2」にデータを挿入します。 たぶんこの場合のinsertはこのようなやり方だと思います（あまり自信ないなぁ・・・） 「tbl2」の行を削除した場合に、「common_tbl」の行も自動で削除させたいと思うのですが、 どのように外部キーを設定するのか（どこに設定するのか）が分かりません。 「common_tbl」に「tbl1」のtbl1_idと「tbl2」のtbl2_idを それぞれ持たせるのはたぶん違うと思いますし・・・ この場合の正しいテーブル構成を含めてアドバイスをお願いしたいです。 例： 「tbl2」のtbl2_idが2の行を削除したら、 「common_tbl」のcommon_idが3の行も削除したい（外部キーで自動的に） テーブル「tbl1」 +------+---------+----------+ | tbl1_id| tbl1_col1 |common_id | +------+---------+----------+ | 1 | 値1 | 1 | +------+---------+----------+ テーブル「tbl2」 +------+---------+----------+----------+ | tbl2_id| tbl2_col1 | tbl2_col2 |common_id | +------+---------+----------+----------+ | 1 | 値1 | 値1 | 2 | +------+---------+----------+----------+ | 2 | 値2 | 値2 | 3 | +------+---------+----------+----------+ テーブル「common_tbl」 +----------+---------+----------+ |common_id | col1 | col2 | +----------+---------+----------+ | 1 | 値1 | 値1 | +----------+---------+----------+ | 2 | 値2 | 値2 | +----------+---------+----------+ | 3 | 値3 | 値3 | +----------+---------+----------+
- ベストアンサー
- MySQL
- 外部キーのリレーションが設定できない
VWD2008でSQLサーバーを使用して簡単な検索DBを作成しようとしています。 元々アクセスのデータベースファイルから SQLサーバーの機能を利用してSQLDBをエクスポートしました。 Shopsテーブルには [id]…主キー(自動採番) [Shop_Code]…販売店コード [Shop_Name]…販売店名 [Shop_Add]…住所 [Shop_AreaID]…Shope_Areaテーブルとリレーションを組む というフィールドが存在します。 Shop_Areaテーブルには [Shop_AreaID]…主キー(自動採番) [Shop_Area_Status]…北海道・仙台・東京・名古屋なとの地域情報が入ります。 ●Shope_Areaテーブル Shop_AreaID ｜ Area_Status ｜ ------------------------------------ 01 ｜ 北海道 ｜ 02 ｜ 仙台 ｜ 03 ｜ 東京 ｜ VWD機能のデータベースダイアグラムより Shopsテーブルの[Shop_AreaID]と Shop_Areaテーブルの[Shop_AreaID]でリレーションを作成し ダイアグラムの保存を試みようとするとエラーダイアグラムが表示されてしまいます。エラーメッセージは以下の通りです。 テーブル 'Shop_Area' は正しく保存されました。 テーブル 'shops' - リレーションシップ 'FK_shops_Shop_Area' を作成できません。 ALTER TABLE ステートメントは FOREIGN KEY 制約 "FK_shops_Shop_Area" と競合しています。競合が発生したのは、 データベース "C:\....\091111_DATA.MDF"、テーブル "dbo.Shop_Area", column 'Shop_Area_ID' です。 上記のようなエラーメッセージが出力される場合 どういった理由が考えられるのでしょうか。 色々試してみたのですがリレーションを設定することができません。 どうか教えてください。
- 締切済み
- Microsoft ASP
- 複数のテーブルの全てのカラムを一度に検索するには？
検索対象のカラムがid(主キー)、bc1～bc40と41個あります。 １つのテーブルに41個のカラムを作り検索させると非常に時間がかかります。 それにインデックスを全部に付けたいですが16個までしかつけられないです。 なので、５つのテーブルにカラムを分けて全部のカラムにインデックスをつけて それぞれを検索させようと思っています。 テーブルが１つだけなら SELECT * FROM `bc` WHERE `bc1`='あいうえおかき' AND `bc2`='あいうえおかき' AND `bc3`='あいうえおかき' AND ・・・ `bc39`='あいうえおかき' AND `bc40`='あいうえおかき' ORDER BY `bc33` DESC LIMIT 0,50; のようにできますが、テーブルが複数の場合はどうしたらよいのでしょうか。 検索を試す前に下記ができるか試してみました。 SELECT COUNT(*) FROM (((`ccc` INNER JOIN `bbb` ON `ccc`.`id`=`bbb`.`id`) INNER JOIN `aaa` ON `ccc`.`id`=`aaa`.`id`) INNER JOIN `ddd` ON `ccc`.`id`=`ddd`.`id`) INNER JOIN `eee` ON `ccc`.`id`=`eee`.`id` エラーにはなりませんがカウント数が0になってしまいました。 SELECT COUNT(*) FROM (`ccc` INNER JOIN `bbb` ON `ccc`.`id`=`bbb`.`id`) INNER JOIN `aaa` ON `ccc`.`id`=`aaa`.`id` これは正確にできてカウント数100000 SELECT COUNT(*) FROM ((`ccc` INNER JOIN `bbb` ON `ccc`.`id`=`bbb`.`id`) INNER JOIN `aaa` ON `ccc`.`id`=`aaa`.`id`) INNER JOIN `ddd` ON `ccc`.`id`=`ddd`.`id` ４つ目のテーブルを入れたところから無理なようです。 他に良い方法はないのでしょうか。
- ベストアンサー
- MySQL
- 複数のテーブルを外部結合する
複数のテーブルを外部結合してSUMをとってきたいとき、SQLServerではどのような結合にすればよいのでしょう。 一対一の関係なら「LEFT JOIN」を使って「FROM テーブルＡ LEFT JOIN テーブルＢ ON テーブルＡ.フィールド = テーブルＢ.フィールド」ができますが、一対複数の関係で書き方がよくわからず、かなり困っています。 例） ユーザーマスター ・ユーザーID ・ユーザー氏名 ○月売上テーブル←○には1～12が入る ・ユーザーID ・売上金額 欲しいデータは ・ユーザーID ・ユーザー氏名 ・SUM(1月売上テーブル.売上金額) 1月 ・SUM(2月売上テーブル.売上金額) 2月 ・SUM(3月売上テーブル.売上金額) 3月 以降、12月まで続く ORACLEだと↓のやり方で、 ・ユーザーマスターはすべて ・各月の売上テーブルはユーザーID毎に集計(外部結合) を出力してくれるのですが…。 SELECT u.ユーザーID,u.ユーザー氏名, SUM(m1.売上金額),SUM(m2.売上金額),SUM(m3.売上金額),,, FROM ユーザーマスター u, 1月売上テーブル m1, 2月売上テーブル m2, 3月売上テーブル m3,,, WHERE u.ユーザーID=m1.ユーザーID(+) AND u.ユーザーID=m2.ユーザーID(+) AND u.ユーザーID=m3.ユーザーID(+),,, GROUP BY u.ユーザーID,u.ユーザー氏名 ORDER BY u.ユーザーID,u.ユーザー氏名
- ベストアンサー
- SQL Server
- 外部キーが設定されているテーブルのupdateについて
PostgreSQL8.24を利用しています。 pgADMINIIIでテーブルを作成しました。 【TABLE_A】と【TABLE_B】があります。 【TABLE_A】の【ID】が【TABLE_B】の【ID2】が外部キーとして設定してあります。 CONSTRAINT TABLE_B_fkey FOREIGN KEY (ID2) REFERENCES TABLE_A (ID) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION 上記のように記述されています。 SQL文のUPDATEを使い、IDが「01」を「05」に更新したいのですが、 以下のように記述するとエラーになります。 どのようなUPDATE文を記述すればよろしいのでしょうか。 UPDATE TABLE_A INNER JOIN TABLE_B on TABLE_A.ID=TABLE_B.ID2 SET TABLE_A.ID = '05',TABLE_B.ID2 = '05' WHERE TABLE_A.ID='01'" よろしくお願いいたします。
- ベストアンサー
- PostgreSQL
- 外部キーの使い方
例えば下記のような2つのテーブルがあり、注文票にはメニューのID（外部キー）だけを入れて、料理名や価格は別で持つとします。 ----------------------------------------- ■注文票table ID(int),メニューID(int) ----------------------------------------- ■メニューtable ID(int),料理名(text) ----------------------------------------- ・この2つのテーブルから以下のような出力を得たい時 且つ ・一度のselect文で出力を得る場合、 select文はどのように書けば良いでしょうか？ もちろん「select * from 注文票table」だと「料理名」ではなくて「料理ID」が出てきてしまうのですが。。。 ---------------------------------------- ■テーブルID,料理名 ----------------------------------------
- ベストアンサー
- PostgreSQL
- 複数のカラムに対するリレーションについて
こんにちは、ER図の書き方（テーブル定義）について勉強しているのですが、皆さんにお聞きしたいことがあります。 ある学校が、毎年のマラソン大会の情報をDBで管理しようとしています。そのマラソン大会は、 ・午前・午後それぞれ別のコースで1回ずつ実施する（参加者は同じ） ・コースの組み合わせは毎回ランダムに決定される という条件で開催されます。 また、コースには難易度が設定されていて、その難易度は ・コース個別ではなく、2つのコースの組み合わせで決定される ・その日の天候（晴れか雨）によって変わる（午前・午後の天候は同一とする） ・50を平均とした100段階評価になっている という条件を持っています。 以上を満たすテーブル構成として、どういったものが考えられるでしょうか？アドバイスをお願いいたします。 ちなみに、以下のようなものを考えてみました。 ======================================================= 大会テーブル ━━━━━━━━━ 開催日ID 午前コースID（FK） 午後コースID（FK） 天候 コーステーブル ━━━━━━━ コースID 距離 難易度テーブル ━━━━━━━ コース1ID（FK） コース2ID（FK） 天候（FK） 難易度 ======================================================= この場合、「大会テーブル」と「コーステーブル」、または 「難易度テーブル」と「コーステーブル」は、それぞれ 2つのカラムでリレーションを持っていて、ER図を書くときに 線が2本（？）になりそうで、どうしようかと迷いました。 以上、よろしくお願いいたします。
- 締切済み
- その他(データベース)
お礼
大変詳しいご説明をしていただき、ありがとうございました。 仕様が拡張されても、なるべくテーブルを増やさずに済むように設計するという方針は、非常に納得できました。