DBのテーブル設計におけるデータの関係性(データモデリング)

SQL

データベースのテーブル設計において、データの関係性は主に次の3つのタイプに分類されます：１対１（One-to-One）、１対多（One-to-Many）、多対多（Many-to-Many）。

表形式を例にして、データの関係を表しますが、SQL(RDB)以外のデータベース、いわゆるNoSQLでもデータの関係性を表すことができます。

 

3つのデータ関係性

３つのデータの関係性についてまず簡単に解説いたします。

 

１対１ (One-to-One) 関係

１対１（One-to-One）関係は、2つのエンティティ（テーブル）間の関係で、一方のエンティティの1つのレコードがもう一方のエンティティの1つのレコードに対応する場合に発生します。この関係は比較的稀で、通常、データの一部を別のエンティティに分割するために使用されます。例として、以下のような状況が考えられます：

人とパスポート: 1つの人には1つのパスポートが対応し、逆もまた然りです。これは１対１の関係です。データベースのテーブルとしては、人とパスポートが別々のテーブルに保存されることが一般的です。

 

１対多 (One-to-Many) 関係

１対多（One-to-Many）関係は、一方のエンティティの1つのレコードが、もう一方のエンティティの複数のレコードに関連付けられる関係です。これはデータベースデザインにおいて最も一般的な関係です。具体的な例を挙げてみましょう：

顧客と注文: 1つの顧客が複数の注文を持つことができます。したがって、顧客と注文の関係は１対多の関係です。顧客テーブルには各顧客の情報が保存され、注文テーブルには各注文の情報が保存され、注文には顧客に対する外部キーが含まれます。

 

多対多 (Many-to-Many) 関係

多対多（Many-to-Many）関係は、複数のエンティティが互いに複数のレコードと関連付けられる関係です。多くの場合、多対多の関係は、中間テーブルを使用して2つの１対多の関係を組み合わせることによって実現されます。具体的な例を考えてみましょう：

学生と科目: 学生は複数の科目を選択し、科目には複数の学生が登録できます。これは多対多の関係です。データベースでは、学生テーブル、科目テーブル、および中間テーブル（通常、学生IDと科目IDを組み合わせたもの）が使用され、学生と科目の関連付けが中間テーブルを介して行われます。

これらの関係性は、データベース設計において非常に重要であり、データモデルの正確性と効率性を確保するために適切に理解する必要があります。データベースの正確な設計は、データの整合性とデータの抽出に大きな影響を与えます。

１対１（One-to-One）関係のデータモデル

2つのエンティティ（テーブル）がお互いに１つのレコードに関連付けられます。通常、このような関係は、データの分割、整理、セキュリティなどの理由から使用されます。以下に、1対1の関係を持つデータモデルの具体的な例を示します。

例: ユーザーとプロフィール

この例では、ユーザー情報とユーザープロフィール情報が１対１の関係を持つデータモデルを考えます。ユーザーテーブルとプロフィールテーブルを作成し、それぞれのテーブルに具体的なデータを格納します。

ユーザーテーブル (users):

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(100) NOT NULL
);

このテーブルには、各ユーザーの基本情報（user_id、username、passwordなど）が格納されます。

 

プロフィールテーブル (user_profiles):

CREATE TABLE user_profiles (
    profile_id INT PRIMARY KEY,
    user_id INT UNIQUE,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

このテーブルには、ユーザープロフィール情報が格納されます。user_idカラムはユーザーテーブルのuser_idと関連づける外部キーであり、ユーザーごとに一意であることが保証されます。

 

ユーザーテーブルとプロフィールテーブルのデータを具体的に挿入すると、次のようになります：

ユーザーテーブルのデータ (users):

user_idusernamepassword
1user1hashed1
2user2hashed2

 

プロフィールテーブルのデータ(user_profiles):

profile_iduser_idfirst_namelast_nameemail
11JohnDoejohn@example.com
22JaneSmithjane@example.com

 

ユーザーテーブルとプロフィールテーブルのuser_idカラムが関連づけられ、各ユーザーには１つのプロフィールが対応しています。このように、１対１の関係を実現することができます。

 

１対多（One-to-Many）関係のデータモデル

1つのエンティティ（テーブル）の1つのレコードが、もう一方のエンティティの複数のレコードに関連付けられます。これは一般的なデータモデルであり、多くの実際のシナリオで使用されています。以下に、1対多の関係を持つデータモデルの具体的な例を示します。

例: 著者（作者）と著書（本）

この例では、著者（作者）と著書（本）の関係を考えます。著者は複数の著書を執筆できますが、著書は通常1人の著者に関連付けられます。したがって、これは１対多の関係です。以下に、著者テーブルと著書テーブルを作成し、それぞれのテーブルにデータを格納する具体的なSQLを示します。

 

著者テーブル (authors):

CREATE TABLE authors (
    author_id INT PRIMARY KEY,
    author_name VARCHAR(100) NOT NULL
);

このテーブルには、著者の基本情報（author_id、author_name）が格納されます。

 

著書テーブル (books):

CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author_id INT,
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

このテーブルには、著書の情報が格納されます。author_idカラムは著書と著者を関連付ける外部キーであり、複数の著書(本)が同じ著者(作者)に関連づけられることができます。

著者テーブルと著書テーブルのデータを具体的に挿入すると、次のようになります：

著者テーブルのデータ (authors):

author_idauthor_name
1J.K. Rowling
2George Orwell

 

著書テーブルのデータ (books):

book_idtitleauthor_id
1Harry Potter Series1
219842
3Animal Farm2

 

このデータモデルでは、著者と著書が１対多の関係を持ち、同じ著者によって執筆された著書が関連付けられています。このように、複数の著書が同じ著者に関連づけられることができます。

 

多対多（Many-to-Many）関係のデータモデル

1つのエンティティ（テーブル）の1つのレコードが、もう一方のエンティティの複数のレコードに関連付けられ、逆も同様です。多対多の関係を表現するためには、通常、中間（またはピボット）テーブルが必要です。以下に、多対多の関係を持つデータモデルの具体的な例を示し、中間テーブルも説明します。

例: 学生と科目

この例では、多対多の関係を持つ学生と科目のデータモデルを考えます。学生は複数の科目を選択でき、一つの科目は複数の学生に関連付けられます。この場合、学生テーブル、科目テーブル、および中間テーブル（通常、学生IDと科目IDを組み合わせたもの）が必要です。

 

学生テーブル(students)

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL
);

 

科目テーブル(subjects)

CREATE TABLE subjects (
    subject_id INT PRIMARY KEY,
    subject_name VARCHAR(100) NOT NULL
);

 

中間テーブル(student_subjects)

CREATE TABLE student_subjects (
    student_id INT,
    subject_id INT,
    PRIMARY KEY (student_id, subject_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (subject_id) REFERENCES subjects(subject_id)
);

この中間テーブルには、学生と科目の関連性が保存されます。student_idとsubject_idは、それぞれ学生テーブルと科目テーブルへの外部キーで、主キー制約を持ちます。

 

学生テーブル、科目テーブル、中間テーブルのデータを具体的に挿入すると、次のようになります：

学生テーブルのデータ (students):

student_idstudent_name
1Student A
2Student B

科目テーブルのデータ (subjects):

subject_idsubject_name
101Math
102Science
103History

中間テーブルのデータ (student_subjects):

student_idsubject_id
1101
1102
2102
2103

 

このデータモデルでは、複数の学生が複数の科目を選択でき、中間テーブルを介して学生と科目が関連付けられています。このように、多対多の関係を効果的にモデル化し、データベース内で表現できます。

 

今回テーブル形式でデータモデルを表現致しました。

SQLのテーブル設計を考える上でデータモデルや正規化という考えは重要になります。

以下の記事ではSQLを体系的に学習できるオンラインコースの概要を紹介しています。

SQLを１からマスターするコースをリリースしました【Udemy】
Udemyコースを初めてリリースいたしました。SQLを1から学んでマスターできる講座となっています。要所に演習を挟んでいるので、ちゃんと受講した内容を理解できているのか確認できるようにしています。正規化やテーブル設計、実行計画の見方、ストアドプロシージャまで学習していきます。
masa-engineer-blog.com
2023.05.17

 

NoSQLの１つであるMongoDB(ドキュメント型DB)でもデータモデルを考えることができます。

以下の記事では、データモデル以外にもMongoDBの基礎を抑えることができるオンラインコースの概要を紹介しています。

MongoDB基礎コースをリリースしました【Udemy】
講座の第2弾として、MongoDBの基礎を学習するコースをリリースすることができました。MongoDBは、NoSQL Databaseであり、またWebアプリ開発でも利用される人気のデータベースの１つです。コースは全体で7時間を超えるボリュームとなっています。
masa-engineer-blog.com
2023.10.04

スポンサー広告