先生と生徒の会話形式で理解しよう

生徒

「データベース設計って何ですか?SQLを書く前に必要なんですか?」

先生

「データベース設計は、データを整理して保存するための設計図を作ることです。家を建てる前に設計図を描くように、SQLを書く前にデータの構造を考える必要があります。」

生徒

「設計図がないとどうなるんですか?」

先生

「設計図なしで家を建てると崩れやすくなるように、設計なしでデータベースを作ると、データが重複したり、必要な情報が取り出せなくなったりします。最初にしっかり設計することが大切なんです。」

1. データベース設計とは何か?

データベース設計とは、データを効率的に保存し、簡単に取り出せるようにするための計画を立てることです。パソコンに詳しくない方にもわかりやすく例えると、図書館の本棚の配置を考えることに似ています。

例えば、図書館では本を「小説」「参考書」「雑誌」などのジャンルごとに分けて、さらに著者名の順番に並べますよね。これと同じように、データベースでも「顧客情報」「商品情報」「注文情報」などに分けて整理します。この整理の仕方を決めるのがデータベース設計です。

データベース設計をしっかり行うことで、後からSQLという命令文を使ってデータを操作するときに、スムーズに情報を取り出したり、更新したりできるようになります。逆に設計が不十分だと、同じデータが何度も保存されたり、必要な情報が見つからなくなったりする問題が起こります。

2. テーブルとは?データを入れる箱の概念

データベースの中でデータを保存する基本的な単位を「テーブル」と呼びます。テーブルは、エクセルの表のようなもので、縦の列と横の行でできています。

縦の列のことを「カラム」または「フィールド」と呼び、横の行のことを「レコード」または「行」と呼びます。カラムには「名前」「年齢」「メールアドレス」などの項目名が入り、レコードには実際のデータが入ります。

例えば、会員管理システムを作る場合、会員の情報を保存する「会員テーブル」を作ります。このテーブルには、会員番号、氏名、生年月日、住所、電話番号などのカラムを用意します。

会員テーブルの例


id | name       | age | address      | phone
---+------------+-----+--------------+-------------
1  | 山田太郎   | 28  | 東京都渋谷区 | 090-1111-2222
2  | 佐藤花子   | 34  | 大阪府大阪市 | 080-3333-4444
3  | 鈴木一郎   | 22  | 愛知県名古屋 | 070-5555-6666
4  | 田中美咲   | 45  | 福岡県福岡市 | 090-7777-8888

このように、テーブルという箱の中にデータを整理して入れることで、後から「東京都に住んでいる会員だけを探す」といった操作が簡単にできるようになります。

3. 主キーとは?データを識別する大切な番号

主キー(プライマリーキー)とは、テーブルの中で各レコードを一意に識別するための特別なカラムです。「一意に識別する」とは、そのテーブルの中で絶対に重複しない値を持つということです。

わかりやすく例えると、学校のクラスで出席番号を考えてみてください。クラスには同じ名前の生徒がいるかもしれませんが、出席番号は必ず一人一つで、同じ番号の人はいませんよね。この出席番号が主キーの役割を果たします。

データベースでも同様に、会員テーブルなら「会員ID」、商品テーブルなら「商品ID」のように、各レコードを確実に区別できる番号を主キーとして設定します。主キーは空っぽ(NULL)にすることができず、必ず値を入れなければなりません。

主キーを設定したテーブルの作成


CREATE TABLE members (
    member_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    join_date DATE
);

このコードでは「member_id」というカラムを主キーとして設定しています。PRIMARY KEYと指定することで、このカラムには重複した値が入らないようにデータベースが自動的に管理してくれます。

4. リレーションシップ(関連性)の理解

リレーションシップとは、複数のテーブル同士の関係性のことです。データベース設計では、一つの大きなテーブルにすべての情報を詰め込むのではなく、関連する情報ごとに複数のテーブルに分けて管理します。

例えば、オンラインショップを考えてみましょう。顧客の情報と注文の情報を一つのテーブルにまとめてしまうと、同じ顧客が何度も注文するたびに、顧客の名前や住所を何度も保存することになります。これはデータの無駄遣いですし、住所が変わったときに全部修正しなければなりません。

そこで、「顧客テーブル」と「注文テーブル」を別々に作り、注文テーブルには顧客IDだけを保存します。こうすることで、顧客の詳細情報は顧客テーブルに一度だけ保存すればよく、注文テーブルからは顧客IDを使って必要なときに顧客情報を参照できます。

顧客テーブル


customer_id | customer_name | phone
------------+---------------+-------------
101         | 高橋健太      | 090-1234-5678
102         | 伊藤美香      | 080-2345-6789
103         | 渡辺誠        | 070-3456-7890

注文テーブル


order_id | customer_id | product_name | order_date
---------+-------------+--------------+------------
1        | 101         | ノートPC     | 2024-12-01
2        | 101         | マウス       | 2024-12-15
3        | 102         | キーボード   | 2024-12-20
4        | 103         | モニター     | 2024-12-25

この例では、注文テーブルの「customer_id」が顧客テーブルの「customer_id」を参照しています。このような参照関係を「外部キー」と呼び、テーブル同士を繋ぐ重要な役割を果たします。

5. 正規化とは?データの重複を防ぐ技術

正規化とは、データの重複や矛盾を防ぐために、テーブルを適切に分割する作業のことです。データベース設計において非常に重要な概念で、データの整合性を保つために欠かせません。

例えば、次のような一つのテーブルですべての情報を管理しているとします。注文番号、顧客名、顧客住所、商品名、商品価格がすべて一つのテーブルに入っている状態です。この場合、同じ顧客が複数回注文すると、その都度顧客名と住所が重複して保存されます。

もし顧客が引っ越して住所が変わった場合、その顧客のすべての注文レコードの住所を修正しなければなりません。一つでも修正し忘れると、同じ顧客なのに違う住所が登録されている矛盾が生まれてしまいます。

正規化を行うことで、このような問題を防ぐことができます。顧客情報は顧客テーブル、商品情報は商品テーブル、注文情報は注文テーブルというように分割し、必要に応じてテーブル同士を関連付けて使います。これにより、データの重複がなくなり、更新も一箇所だけで済むようになります。

正規化のメリット

データの重複が減り、ストレージ容量を節約できる
データの更新が簡単になり、矛盾が起こりにくくなる
データの整合性が保たれ、信頼性が高まる

6. データ型の選択

データベース設計では、各カラムにどのような種類のデータを保存するかを決める必要があります。これを「データ型」と呼びます。データ型を正しく選ぶことで、データベースの動作が速くなり、無駄な容量も使わずに済みます。

主なデータ型には以下のようなものがあります。整数を保存するINT型、小数を保存するDECIMAL型やFLOAT型、文字列を保存するVARCHAR型やTEXT型、日付を保存するDATE型、日時を保存するDATETIME型などです。

例えば、年齢を保存するなら整数のINT型、名前を保存するなら文字列のVARCHAR型、生年月日を保存するならDATE型を選びます。電話番号は数字だけですが、計算に使わないのでVARCHAR型で保存するのが一般的です。

適切なデータ型を使ったテーブル作成


CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(200),
    price DECIMAL(10, 2),
    stock_quantity INT,
    release_date DATE
);

このコードでは、商品IDは整数、商品名は最大200文字の文字列、価格は小数点以下2桁までの数値、在庫数は整数、発売日は日付型として定義しています。それぞれのデータの特性に合わせて適切な型を選ぶことが重要です。

7. インデックスとは?検索を高速にする仕組み

インデックスとは、データベースの検索速度を速くするための仕組みです。本で例えると、巻末にある索引のようなものです。索引があれば、本の最初から順番に読まなくても、目的のページをすぐに見つけられますよね。

データベースでも同じで、インデックスを設定すると、大量のデータの中から特定の情報を探すときに、すべてのレコードを一つずつ調べる必要がなくなります。特によく検索するカラムにインデックスを設定しておくと、SQLの実行速度が大幅に改善されます。

ただし、インデックスにもデメリットがあります。インデックスを作成すると、その分だけストレージ容量を使いますし、データを追加・更新・削除するときにインデックスも一緒に更新する必要があるため、書き込み処理が少し遅くなります。そのため、すべてのカラムにインデックスを付けるのではなく、よく検索するカラムだけに絞って設定することが大切です。

主キーには自動的にインデックスが設定されますが、それ以外で頻繁に検索条件として使うカラム、例えば会員テーブルのメールアドレスや、商品テーブルの商品コードなどにインデックスを追加すると効果的です。

8. NULL値の扱い方

NULL(ヌル)とは、データが存在しないことを表す特別な値です。空っぽや未入力という意味で、ゼロや空文字とは異なります。例えば、会員登録時に電話番号が任意項目の場合、入力されなかったらNULLとして保存されます。

データベース設計では、各カラムがNULL値を許可するかどうかを決める必要があります。必須項目のカラムには「NOT NULL」という制約を付けて、NULL値が入らないようにします。逆に、任意項目のカラムはNULLを許可します。

NULL値を扱うときは注意が必要です。SQLで検索するときに、通常の比較演算子ではNULL値を正しく扱えません。NULL値かどうかを調べるには「IS NULL」または「IS NOT NULL」という特別な書き方を使います。

9. ER図でデータベース構造を可視化する

ER図(Entity-Relationship図)とは、データベースの構造を図で表したものです。エンティティ(テーブル)同士の関係性を視覚的に示すことで、データベース設計の全体像を理解しやすくします。

ER図では、四角形でテーブルを表し、線でテーブル同士の関係を表します。例えば、顧客テーブルと注文テーブルが「一人の顧客は複数の注文ができる」という関係にある場合、顧客から注文に向かって線を引き、「1対多」の関係であることを示します。

データベース設計を始めるときは、いきなりSQLを書くのではなく、まずER図を描いて全体の構造を考えることが推奨されます。紙に手書きでもよいですし、専用のツールを使ってもよいでしょう。ER図があれば、チームメンバーとのコミュニケーションも円滑になります。

10. 実際のテーブル作成とデータ投入

ここまで学んだ概念を活かして、実際にテーブルを作成してデータを投入してみましょう。シンプルな書籍管理システムを例に、書籍テーブルを作成します。

テーブル作成のSQL


CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author VARCHAR(100),
    price INT,
    published_date DATE
);

次に、作成したテーブルにデータを投入します。INSERT文を使って、書籍情報を登録していきます。

データ投入のSQL


INSERT INTO books (book_id, title, author, price, published_date)
VALUES (1, 'データベース入門', '山田太郎', 2800, '2024-01-15');

INSERT INTO books (book_id, title, author, price, published_date)
VALUES (2, 'SQL実践ガイド', '佐藤花子', 3200, '2024-03-20');

データ投入後のテーブル


book_id | title              | author     | price | published_date
--------+--------------------+------------+-------+---------------
1       | データベース入門   | 山田太郎   | 2800  | 2024-01-15
2       | SQL実践ガイド      | 佐藤花子   | 3200  | 2024-03-20
3       | Web開発の基礎      | 鈴木一郎   | 2500  | 2024-05-10
4       | Python入門         | 田中美咲   | 2900  | 2024-06-18
5       | ネットワーク技術   | 伊藤健太   | 3500  | 2024-08-22

このように、まずテーブルの構造を定義してから、データを投入していきます。設計をしっかり行っておけば、後からデータを追加したり検索したりする作業がスムーズに進みます。