データベース設計ルールの基本

前書き

よくあることですが、データベースアーキテクトは、特定のソリューション用のデータベースを開発する必要があります。

ある金曜日の夜、仕事帰りの電車の中で、さまざまな会社の従業員を雇うためのサービスをどのように作成するかを考えました。結局のところ、既存のサービスのいずれも、候補者があなたにどれほど適しているかをすばやく理解することはできません。特定のスキル、プロジェクト、またはポジションのセットを含む、または除外する複雑なフィルターを作成することはできません。サービスが通常提供する最大値は、会社別および一部はスキル別のフィルターです。



この記事では、技術的な情報と人生の非技術的な例を混ぜ合わせることで、資料の厳密な表現を少し薄めることができます。



まず、ジョブ検索サービス用のMS SQLServerでのデータベースの作成を分析してみましょう。



この資料は、MySQLやPostgreSQLなどの別のDBMSに転送できます。

デザインルールの基本

データベーススキーマを設計するには、7つの正式なルールと、正規化および非正規化の概念そのものを覚えておく必要があります。それらはすべてのデザインルールの基礎です。



7つの正式なルールについて詳しく説明しましょう。

1. 1対1の関係：
   
   
   
   1.1）必須の接続：
   
   
   
   例として、市民とそのパスポートがあります。すべての市民はパスポートを持っている必要があります。市民ごとに1つのパスポート
   
   
   
   この関係は、2つの方法で実装できます
   
   
   
   。1.1.1）1つのエンティティ（表）：図1。 Citizenエンティティ ここで、Citizenテーブルは市民エンティティであり、PassportData属性（フィールド）には市民のすべてのパスポートデータが含まれ、空にすることはできません（NOTNULL）。 2）2つの異なるエンティティ（テーブル）：図2。 CitizenエンティティとPassportDataエンティティの関係
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   ここで、Citizenテーブルはcitizenエンティティであり、PassportDataテーブルは市民のパスポートデータエンティティ（パスポート自体）です。シチズンエンティティには、PassportDataテーブルのプライマリキーを参照するPassportID属性（フィールド）が含まれています。次に、パスポートデータエンティティには、CitizenテーブルのプライマリキーCitizenIDを参照する属性（フィールド）CitizenIDが含まれています。シチズンテーブルのPassportIDフィールドを空にすることはできません（NOTNULL）。 PassportDataテーブルのCitizenIDフィールドの整合性を維持して、1対1の関係を確保することも重要です。つまり、CitizenテーブルのPassportIDフィールドとPassportDataテーブルのCitizenIDフィールドは、1.1.1節に示されているのと同じエンティティ（テーブル）であるかのように、同じレコードを参照する必要があります。
   
   
   
   1.2）オプションのリンク付き：
   
   
   
   例としては、特定の国のパスポートを持っている人と持っていない人があります。前者の場合、彼は問題の国の市民になり、後者の場合、彼はそうではありません。
   
   
   
   この関係は、2つの方法で実装できます
   
   
   
   。1.2.1）1つのエンティティ（表）：図3。 PersonエンティティPerson テーブルはPersonのエンティティであり、PassportData属性（フィールド）にはすべてのパスポートデータが含まれており、空（NULL）にすることができます。 2）2つのエンティティ（テーブル）：図4。 PersonエンティティとPassportDataエンティティ間の関係
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   Personテーブルは個人のエンティティであり、PassportDataテーブルは個人のパスポートデータエンティティ（パスポート自体）です。ヒューマンエンティティには、PassportDataテーブルのプライマリキーを参照するPassportID属性（フィールド）が含まれています。次に、パスポートデータのIDには、PersonテーブルのプライマリキーPersonIDを参照する属性（フィールド）PersonIDが含まれます。 PersonテーブルのPassportIDフィールドはNULLにすることができます。 PassportDataテーブルのPersonIDフィールドの整合性を維持することも重要です。これは、1対1の通信を確保するために必要です。 PersonテーブルのPassportIDフィールドとPassportDataテーブルのPersonIDフィールドは、1.2.1節に示されているのと同じエンティティ（テーブル）であるかのように、同じレコードを参照する必要があります。または、これらのフィールドはnullである必要があります。つまり、NULLが含まれている必要があります。
   
   
2. :
   
   
   
   2.1) :
   
   
   
   . .
   
   
   
   :
   
   
   
   2.1.1) ():
   
   
   
   
   
   .5. Parent
   
   
   
   Parent , () ChildList . (NOT NULL). ChildList (NoSQL) XML, JSON .
   
   
   
   2.1.2) ():
   
   
   
   
   
   .6. Parent Child
   
   
   
   Parent , Child — . Child ParentID, ParentID Parent. ParentID Child (NOT NULL).
   
   
   
   2.2) :
   
   
   
   , .
   
   
   
   :
   
   
   
   2.2.1) ():
   
   
   
   
   
   .7. Person
   
   
   
   Parent , () ChildList . (NULL). ChildList (NoSQL) XML, JSON .
   
   
   
   2.2.2) ():
   
   
   
   
   
   .8. Person Child
   
   
   
   Parent , Child — . Child ParentID, ParentID Parent. ParentID Child (NULL).
   
   
   
   2.2.3) , () () :
   
   
   
   
   
   .9. Person
   
   
   
   () Person () ParentID, PersonID Person (NULL).
   
   
   
   « » .
   
   
3. :
   
   
   
   . , «» «», , . , , .
   
   
4. :
   
   
   
   : , . , .
   
   
   
   , NoSQL, , . , 3 ():
   
   
   
   
   
   .10. Person RealEstate
   
   
   
   Person RealEstate . () () PersonRealEstate. () PersonID RealEstateID PersonID Person RealEstateID RealEstate . , PersonRealEstate (PersonID; RealEstateID) PersonRealEstate.
   
   
   
   . , . , 1.1.2 1.2.2.
   
   

1対多および多対1の 関係は、3つ以上のエンティティを介して実装できます。このために、必要な対応するエンティティのプライマリキーを参照する必要な属性が追加されます。この実装は、上記の1.1.2項および1.2.2項で説明した例と同様です。



7つの正式なルールはどこにありますか？



はい、どうぞ：

1. 条項1（条項1.1および条項1.2）-第1および第2の正式な規則
2. 条項2（条項2.1および条項2.2）-3番目および4番目の正式な規則
3. 条項3（条項2と同様）-5番目と6番目の正式な規則
4. 項目4-第7の正式な規則

上記のテキストでは、これらの7つの正式なルールは4つの機能ブロックにグループ化されています。正規化



について話すときは、その本質を理解する必要があります。正規化すると、情報ストレージの再現性が低下するため、データの異常の可能性が低下します。ただし、エンティティを分割する際の正規化は、データ操作（挿入、変更、選択、および削除）のためのより複雑なクエリ構築につながります。 逆正規化プロセスは非正規化と呼ばれます。これは、エンティティの集約とネストによるデータアクセスクエリの構築を簡素化したものです（たとえば、不完全に構造化されたデータ（NoSQL）を使用した上記の2.1.1項と2.2.1項に示されています）。 これがデータベース設計ルールの要点です。











7つの正式なルールの関係を理解し​​ていますか？あなたは理解しました、そして認識しませんでしたか？結局のところ、知ることと理解することは、2つのまったく異なる概念です。



もっと詳しく説明します。エンティティによって拡大されていますが、任意のサブジェクト領域および任意の情報システムのデータベースモデルを数時間でスケッチできますか？ （微妙な点や詳細は、アナリストや顧客担当者に尋ねることで完成できます）。質問があなたを驚かせ、これが不可能だと思うなら、あなたは7つの正式な規則を知っていますが、それらを理解していません。



何らかの理由で、多くの情報源は、これらの関係が発明されたのではなく、明らかにされたという事実を無視しています。それらは当初、実世界の被験者間および被験者とオブジェクト間の両方に存在します。



また、この関係は、1対1対1対多、多対1または多対多。コミュニケーションの義務は変わることも変わらないこともあります。



7つの正式な規則の知識から、私がこれらの関係を正確に理解するようになった1つのケースについてお話ししましょう。



かつて、大学ではこの7つの正式なルールを知っていたので恥ずかしかったのですが、産業慣行（大学は学生をさまざまな企業に派遣して専門的な経験を積む）で、非常に長い間、さまざまな分野の拠点のモデルを構築しました。考えてみたら、この関係がわからないことに気づきました。



人を観察することは私を助け、関係の本質は夢の中で明らかにされました。この夢を簡略化した形で言い直します。他のすべてを詳しく説明することなく、これらの7つの正式なルールをよりよく理解できるものだけです。



夢は、父、母、子供がいる家族についてでした。父親は車の事故で亡くなり、母親は飲み始め、子供たちはやがて孤児院に連れて行かれます。これらの子供たちは長い間両親なしで残されています。それから何人かの子供は保護者を持っています、彼らの何人かもいます。



被験者間にどのような関係があり、これらの関係がどのように変化したかを追跡しましたか？

よく見てみましょう。

• 家族が完成したとき、数人の子供がいて、親と子の関係は多対多でした。
• , . , , , , .
• .
• — .
• , : , ().

夫婦の関係は、正式な結婚では必須の絆と1対1で、または登録前にオプションの絆で1対1です。夫が1人しかいないのと同じように、妻も1人しかいません。少なくともロシアでは。しかし、他の国では、一夫多妻制が可能であり、その後、夫と妻の間の関係は次のようになります多くの1つ、と妻と夫の間- 1に多くの。



うまくいけば、これらの7つの正式なルールの理解にかなり近づくことができます。



常に練習する価値があります。人を観察し、被験者間および被験者とオブジェクト間の既存の関係を特定します。上記は、市民と彼のパスポートを必須のリンクとの1対1の関係として説明しました..。同時に、人とそのパスポートの例は、オプションのリンクとの1対1の関係です。



7つの正式なルールを理解すると、あらゆる情報システムのあらゆる複雑さのデータベースモデルを簡単に設計できます。



また、関係を実装する方法はたくさんあり、関係自体が変わる可能性があることもわかります。データベースモデル（スキーマ）は、特定の時点におけるエンティティ間の関係のスナップショットです。そのため、将来の変化を考慮して、エンティティ自体（実世界またはサブジェクト領域からのオブジェクトのイメージ）とそれらの相互関係を決定することが重要です。



実際またはドメイン内の関係の変化を考慮に入れて適切に設計されたデータベースモデルは、数年または数十年も変更する必要はありません。これは、変更によって大量のデータ（数ギガバイトから数テラバイト）の再保存が必要になるデータウェアハウスにとって特に重要です。リレーショナルモデルのテーブルはエンティティリレーションシップであり、行（タプル）はこれらのリレーションシップのインスタンスであること



を覚えておくことが重要です。ただし、簡単にするために、テーブルはエンティティとして理解されることが多く、テーブルの行はエンティティインスタンスです。それらの関係は、外部キーの形式の関係を通じて表現されます。

求職者を見つけるためのデータベーススキーマの設計

パート1でデータベース設計ルールの基本について説明した後、求職者を見つけるためのデータベーススキーマを作成しましょう。



まず、候補者を探している会社の従業員にとって何が重要かを定義しましょう。

1. HRの場合：
   
   
   
   1.1）申請者が働いていた会社
   
   1.2）これらの会社で申請者が以前に保持していた役職
   
   1.3）申請者が仕事で使用したスキルと能力。
   
   また、各ポジションにおける各企業の申請者の職務期間、および各スキルと能力の使用期間
   
   
2. 技術スペシャリストの場合：
   
   
   
   2.1）これらの会社で申請者が保持する役職;
   
   2.2）申請者が仕事で使用したスキルと能力;
   
   2.3）申請者が参加したプロジェクト;
   
   各ポジションおよび各プロジェクトでの申請者の作業期間、各スキルおよび能力の使用期間
   
   

まず、必要なエンティティを特定しましょう。

1. 社員
2. 会社
3. 位置（位置）
4. 事業
5. スキル

• 企業と従業員は次のようにしている多くの多くの従業員が複数の企業で仕事ができることから、多くの人々が会社のために働きます。
• 役職と従業員は同様の方法で関連しています。1つまたは複数の会社内で複数の従業員が1つの役職を占めることができます。
• , , . , — .
• : .
• , .
• .

従業員が特定の会社の特定の役職および各プロジェクトで働いた時間を記録することは非常に重要であるため、データベースのスキームは次のようになります。求職者を 検索するためのデータベーススキーマここで、JobHistoryテーブルは、各求職者のジョブ履歴のエンティティとして機能します。つまり、従業員、会社、役職、プロジェクトのエンティティ間の多対多の関係を紹介する履歴書です。 プロジェクトやスキルのように互いに関連の多くに多く、したがってProjectSkillエンティティ（テーブル）を介して互いに通信します。

















サブジェクト間およびサブジェクトとオブジェクト間の関係（すでに述べた7つの正式なルール）を理解すると、このスキームまたは同様のスキームを「ひざまずいて」、1枚の紙に1時間以内で実装できます。そして、これは実り多い一日の後の疲労も考慮に入れています。



ここで、「スキル」がXML、JSONの形式の不完全な構造化データ（NoSQL）を介して「プロジェクト」エンティティに埋め込まれている場合、または単にセミコロンで区切られたスキルの名前をリストする場合、データ追加スキームを簡略化することができました。しかし、それではスキルによるサンプリングとスキルによるフィルタリングが難しくなります。



同様のモデルがGeeckoプロジェクトデータベースの中心にあります。



ご覧のとおり、データベース設計の観点から、情報システムの設計に複雑なことは何もありません。これは、データベーススキーマの「エンティティ」に転送された、現実からのオブジェクトとサブジェクトの単なる反映です。これらのエンティティ間の関係は、将来の変更を考慮して、特定の時点で固定されます。



私たちが現実から正確に何を取り、それをスキームの本質に取り入れ、モデルにどのような関係を構築するかは、現在および将来の情報システム全般に何を求めるかによって異なります。つまり、現在および将来の特定の時間の後に受信したいデータ。

ちょっとした歌詞

モデルを操作した後、少し立ち止まって考えてみてください。新しい小さな世界が作成されたばかりです。実世界からの独自のエンティティと独自の関係があります。はい、これはデジタルの世界ですが、今では独自の方法で開発されます。彼は、独自のルールに従って作成された他のシステム（ワールド）と通信（統合）します。データは、生体内の血液のようにこれらのシステムに流れます。



そして、寝る前に、7つの正式な規則が常にあり、それらが私たちをどこにでも取り囲んでいるという事実について考えてください。それ以上でもそれ以下でも、常に7つ。実生活におけるすべての関係は、これらの7つの正式なルールに分解できます。そして、あなたが考えたり夢を見たりするとき、エンティティはどのように相互に関連していますか？同じ7つの正式なルールに従っていませんか？



一般的に、この関係（7つの正式な規則）は、非常に優れた心理療法士、おそらく女性によって明らかにされたと確信しています。情報技術の概念が登場するずっと前のことです。そして最も興味深いのは、これらの関係がデータベースとITの外部に存在することです。後者は、情報システムのモデル化にのみ使用します。



しかし、私たちは少し話題から外れました。新しいシステムを作成する瞬間に、あなたの魂でこのプロセスに取り組むことをお勧めします。そして、私を信じてください、創造の瞬間が起こります。このように設計されたシステムは、デジタル世界のすべての生物の中で最も生き生きとしています。

あとがき

例の図は、SQLServerのデータベース図ツールを使用して実装されました。ただし、DBeaverにも同様の機能があります。

ソース

• 例を使用したSQLデータベース設計の基本
• Geecko
• MicrosoftSQLドキュメント
• SSMS
• SQLServerのデータベースダイアグラムツール