次のER図があるとします。
ここで、iv id =の外部キーを使用して関係を表現した場合Studentの “8112119d1e”>
では、NULLの値を持つことができます（StudentはSchool）に属する必要はありません。例：
したがって、（私が読んだ内容に基づいて）正しい方法は、関係を表す交差テーブルを作成することです。例：
この方法では、NULLの値はテーブルSchool_has_Studentに存在できます。
しかし、交差テーブルを作成する代わりにNULL可能外部キーを使用することの欠点は？
編集：
誤って（school_id、student_id）をテーブル。関係を多対多にしました。正しい主キーはstudent_idである必要があります：
コメント
- あります'はありません"正しい"の方法。 'あなたのニーズに最適な方法があります。
- 誤った前提についてDocに同意しますが、おそらくそれは'まだ答えるのに十分明確ですか？
- 誤った前提がありますが、違いを正して説明するのは簡単です。
- 私は近い投票を撤回しました、しかし文 "したがって、正しい方法（私が読んだものに基づく）は、関係を表す交差テーブルを作成することです" は、これが"正しい"の方法であると言ったひずみソースを教えてください。私が以前に読んだすべての教科書では、1：n関係の標準的な方法は単一の外部キーです。それとも何かを誤解しましたか？
- @Doc Brown 'どこで読んだか覚えていませんが、交差点のテーブルは正しい方法。とにかく、1：nの関係（：1側のオプションの参加を含む）は単一の外部キーを使用して表す必要があるという本の名前を教えてください。私は彼らがこの主題について言っていることを読むことに興味があります。
回答
2つのモデルは異なる関係を表します。
結合テーブルを使用する、多対多の関係をモデル化しています。
単純な外部キーを使用することで、1対多の関係をモデル化しています。
null可能な外部キーの欠点キーは、関係を多対多でモデル化できないことです。それが達成しようとしている場合です。
質問の編集に基づいて、学生テーブルを効果的に分割しています。同じキーを持つ2つのテーブルに。これは通常、フィールドが多すぎるテーブルで見られるため、管理しやすいように2つに分割します（豚に口紅を付けると呼びます）。
生徒のテーブルを分割すると、次のようになります。 2番目のテーブルにはレコードが存在する必要がないため、2番目のテーブルはオプションです。これは、nullになる可能性があるため、設定する必要のないフィールドと非常によく似ています。
1対多の関係が必要な場合は、単一のテーブルを使用して学校IDを許可する方がはるかに優れています。学生テーブルではnullになります。外部キーであっても、フィールドでnullを回避する理由はありません。これは、外部関係がオプションであることを意味します。開発者とDBAはそれを明確に理解しており、基盤となるデータベースエンジンは確かに正常に機能するはずです。
結合について心配している場合でも、心配しないでください。結合がnullフィールドでどのように機能するかについては明確なセマンティクスがあります。 1つのテーブルを使用することで、3つではなく2つのテーブルを結合できます。
コメント
- つまり、1対多の関係をモデル化している場合（：1側のオプションの参加あり）、
NULL値を持つことができるという事実にもかかわらず、外部キーを使用する必要がありますか？ - @Tomはい、それまさにそれをモデル化する方法です。結合テーブルを使用することは技術的には可能ですが、データモデルでは多対多が可能であるため、それを防ぐためにトリガーとデータベースロジックが必要になります。誤ったデータを追加できないように関係を制限することをお勧めします。
- 質問を編集しました。
student_idをSchool_has_Studentテーブルの主キーにしただけで、1対多の関係が維持されました。このメソッドには、外部キーを使用する場合に比べてどのような欠点がありますか？ - @Tom回答を編集しました。
回答
上記のコメントに書き込みました：
「データベースシステムの基礎」という本[…]は[.. 。]外部キー列にNULL値が多数ある場合（たとえば、従業員の98％が部門を管理していない場合）、交差テーブルを使用することをお勧めします。
外部キー列に多くのNULL値がある場合、プログラムは、処理するすべてのレコードについて、このほとんど空の列を処理する必要があります。この列は、おそらくディスク領域を占有します。すべてのケースの98％が空ですが、関係をクエリすることは、より多くのネットワークトラフィックを提供するその列をクエリすることを意味し、テーブルからクラスを生成するORMを使用している場合、プログラムはクライアントでより多くのスペースを必要とします必要以上にサイドection tableはこれを回避し、同等の外部キーがNULLにならない場合に必要なリンクレコードのみが存在します。
それとは反対に、NULL値が少ない場合は、50％以上とします。関係はNULLではありません。交差テーブルを使用すると、逆の効果が得られます。つまり、ディスクスペースが増え、複雑さが増し、ネットワークトラフィックが増えるなどです。
したがって、交差テーブルの使用は最適化の一形態であり、特定のケース、特に最近では、ディスクスペースとメモリが安くなり、必要になる頻度ははるかに少なくなっています。なお、「データベースシステムの基礎」はもともと20年以上前に書かれたものであり（1994年から第2版への参照を見つけました）、その時点ですでに推奨があったと思います。 1994年以前は、大容量記憶装置は依然として高価であり、コンピューターとネットワークは今日よりもはるかに低速であったため、スペースの最適化はおそらく今日よりもはるかに重要でした。
厄介なコメントの補足として：上記のステートメントは、「データベースシステムの基礎」の作成者が彼の推奨事項を念頭に置いていることを予測しようとしているだけです。彼は、ほとんどのシステムに有効な大まかな一般的なステートメントを作成していたと思います。一部のデータベースでは、交差テーブルの使用をさらに廃止する「スパース列」のような他の可能な最適化があります。
したがって、その推奨事項を誤解しないでください。本には書かれていません。一般に{0,1}:n関係の交差テーブルを優先するか、または-あなたが書いたように-これが「正しい方法」であるということです。このような最適化を使用すると、プログラムがより複雑になるのは次の場合のみです。本当に必要です。
コメント
- あなたは'の実装について多くのことを想定しています。データベース、特にOPが'特定のものについて言及していなかったことを考えると'は、データベースが使用するのに十分スマートである可能性が高いです。スパース列用のスペースはごくわずかです。
- @gardenhead：これが"可能性が高いと思われる理由"？
- データベースが持っているという事実何十年も前から存在しており、ほとんどのインフラストラクチャの重要なコンポーネントであるため、高度に最適化されています。
- @gardenhead：私よりもはるかに重い不当な仮定をしているようです。それでも、私の編集を参照してください。
回答
概念モデルは次のようになります。非常に非正統的な控えめに言っても：
物理モデルは次のようになります。これは、混乱します控えめに言っても（よく見ない限り、人々はM：Mだと思うでしょう）：
私の提案：
必要に応じて、ほとんどの生徒には当てはまらない多くの列（FKまたはその他）を使用して、テーブルを1：1 relのロールテーブルに分割します。ただし、FKであるためではありません。これは、列がほとんどの行に適用されないためです。
それ以外の場合、 null許容FKはデータベースの通常の部分であり、結合テーブルは通常M：Mrels用です。
1：1relの一般的な使用法はで、エンティティが特定のタイプの場合にのみ適用される列を持ち、パフォーマンスまたはストレージの考慮事項のためにBLOB列を抽出します。 FKでnull値を回避することは、その一般的な使用法の1つではありません。
回答
他の回答に加えて、外部キーのnull値があいまいであることを指摘したいと思います。意味は次のとおりです。
1）生徒の学校（ある場合）が不明です（これは「null」の標準的な意味です-値は不明です）
2）生徒に学校があるかどうかがわかっていて、学校がない
nullの標準的な意味を使用する場合、外部キーモデルで「生徒に学校がない」をどのように表現しますか。おそらく、学校のテーブルに独自のIDを使用して、「学校なし」のエントリを作成する必要があります。（理想的ではありません）
コメント
- 本 "データベースシステムの基礎" には、
NULL、次のことを意味します：1）不明な値。2）使用できない値または保留された値。3）該当しない属性（この解釈は、NULLは外部キーの場合）。 - その'は便利なリストですが、null（または実際には任意の値）のセマンティクスはユーザーが定義できます。つまり、そのリストに限らず、デザイナーが言うことを何でも意味することができます。問題は、複数の意味が必要になる可能性がある場合（または意図せずに保存される場合）に異なる意味を区別する方法です。
- では、null許容の外部キーを使用する代わりに交差テーブルを作成することを提案していますか？
- @Tomはい、この場合はそれが良いと思います
- @ BradThomas-交差テーブルを使用するときに同じ曖昧さを避けるために、ケース2を表しますか（学生が学校がありません）NULLのSchool_IDを持つ交差テーブルのレコードによって？
回答
データベーステーブルにはこれがあります制約と呼ばれる素晴らしいもの。したがって、交差テーブルで作成するのは非常に簡単で、各生徒の1人だけがテーブルに表示されますが、そのテーブルには多くの学校が表示されます。効果的に
理論は素晴らしいですが、最終的には「あなたが尋ねている質問に基づいてデータベースをモデル化します。
「私の学校にはどの生徒がいますか」という質問で頻繁に質問したい場合は、本当に生徒のテーブル全体にクエリを実行しますか、それとも簡単な交差テーブル。
データベースの場合：質問に合わせて最適化します。
回答
あります3番目のテーブルを使用することが実際に意味がある場合の使用例です。この例は純粋に架空のように見えるかもしれませんが、私の主張をうまく説明していることを願っています。 studentsテーブルにさらに列を追加し、ある時点で、複数の列の複合インデックスを介してレコードに一意性を適用することを決定したとしましょう。 「school_id列も含める必要がありますが、ここでは問題が発生し始めます。SQLの設計方法により、はNULLが可能になります。技術的な観点からは完全に理にかなっていますが、直感に反し、予期しない結果につながる可能性があります。一方、独自性を強制する交差テーブルは簡単です。
最近、このような「オプションの」関係をモデル化する必要がありました。一意性制約の要件はタイムスタンプ列によるものでした。テーブルにnull可能な外部キーを残すと、突然、同じタイムスタンプのレコードを挿入する可能性（監査/承認されていないレコードに設定されたデフォルトのレコードであると仮定しましょう）まだoved）-そして唯一の解決策はnull許容列を削除することでした。
ご覧のとおり、これはかなり特殊なケースであり、他の人が指摘しているように、ほとんどの場合、すべてで完全に問題ありません。 NULLの値。それは実際にはモデルの特定の要件によって異なります。
回答
すでに提出された多くの優れた提案に加えて、個人的に私は「本当に必要でない限り、外部キーのファンではありません。最初に、参照しているM：M関係があります。さらに、外部キーを呼び出して、そのテーブルデータをクエリに取り込むと、複雑さが増し、テーブルサイズ、パフォーマンスの低下。他の人が言っているように、null可能なFKフィールドはサポートされていない可能性があり、データの整合性の問題を引き起こす可能性があります。
学生の学校が不明または空の状態を定義している場合、NULLこれらの条件を区別しません。（ここでも、データの整合性に戻ります。）Tulainsによるロールテーブルの提案はエレガントであり、null値をきれいに許可します。