SQLデータベース設計の基本総おさらい

◆ 目的
以下よくありそうな３つのデータベースを作ることを目的とする。

【小さなお店の売り上げ管理用データベース】
【ブログシステム用データベース】
【オンラインショップ用データベース】

参考にしたのは技術評論社の『データベース エキスパートへの道』。

技術書とというよりかは、ストーリー性のある、どこかの企業のデータベース部署に入社した社員のコンサルタントとしての成長物語である。

まるで自分がそこで働いているかのように実社会人をエミュレートできるため、内容が頭に入ってきやすい。おすすめである。

データベースを作る前に基本は設計の基本をおさえておこう。

◆ データベース設計の流れ

次の●印の流れで設計を検討するするのが王道だろう。

● ER図(Entity Relationship Diagram)の作成
① 実体(entity)の列挙
② 属性(attribute)の追加
④ 関連(relationship)の記述
⑤ 多重度の記載(cardinality)の記述 e) 1対多 など
⑥ 主キー、外部キー検討

● 正規化の検討
ER図が明確に作成できれば、正規化はある程度完成しているだろう。

● DDL(Data Definition Language:データ定義言語)の作成
つまり、SQL言語でテーブルを作成する。



一応正規化も復習しなおす。
◆ 正規化の復習
(1) 第一正規化(1NF ※Normal Forms)
① 列の値が原始的で分解できない。
② 繰り返しが含まれてない。


(2) 第二正規化(2NF)
① 1NFであること。
② 主キーに、主キー以外のすべてのキーが従属している。

以下赤は主キー、青は非主キー、*がついた属性は外部キーとする。

(例 1NFまでの状態)
服ID　靴ID　服色　靴色　価格
服IDと靴IDが複合主キーである。

キーを構成する一部のキーが他項目と従属関係にあるかどうか確認して分離する。

服IDに服色は関係従属(服IDに服色は従属している)。
靴IDに靴色は関係従属(靴IDに靴色は従属している)。

関係従属しているものを別テーブルに抜き出す。
ただし、関係従属させている側(服ID、靴ID)は元のテーブルにそのまま残す。

(例 2NF化する)
服ID* 靴ID*　価格
服ID　服色
靴ID　靴色

(3) 第三正規化(3NF)
① 2NFであること
② 主キー以外のキーは、推移的従属していない。
 キーではない列のどれかが、キーではないほかの列のどれかと関係をもたない。

今度は服と靴の関係ではなく以下のようなテーブルを考えてみる。
(例 2NFまでの状態)
社員番号　指名　部署　勤務地
00000001   A    a     ⅰ
00000002   B    b     ⅱ
00000003   C    a     ⅰ

社員番号が主キーである。
部署に勤務地が推移的従属している。

推移的従属しているものを別テーブルに抜き出す。
ただし、推移的従属させているキーは元のテーブルにそのまま残す。

(例 3NF化する)
社員番号　指名　部署
00000001   A    a
00000002   B    a
00000003   C    b

部署　勤務地
  a      ⅱ
  b      ⅰ



◆ その他、設計前の考慮事項
以下の制約を入れるかどうかを考える必要がある。

・主キー制約
同じ値が入ることは許されない。NULLは許されない。

・NOT NULL制約
NULL値は許可されない。

・ユニーク制約
同じ値が入ることは許されない。NULLは許される。

・CHECK制約
0以上100以下などの値の範囲を制限する。

・外部キー制約(参照整合性制約)
外部キーが参照する列は主キー、もしくはユニーク制約がついている必要がある。
ユニーク制約も許されるということは外部キーにはNULLが含まれていてもよい。
外部キーがNULLとは、親テーブルに値が存在しないことを意味する。
この制約を用いることで親テーブルに値が存在していることを保証できる。



ようやく準備が整った。ここから具体的なデータベースを設計する。
◆ データベース設計
当然だが、仕様によって設計は大きく変わる。
仕様内容は記載しないが、適当な状況を想定した一例だと思い見てほしい。

【小さなお店の売り上げ管理用データベース】 レベル1
● 実体を決め、そこに属性を追加する

実体(エンティティ)
　属性1　属性2　属性3・・・　と記載する
　※赤は主キー、*がついた属性は外部キーとする。

カーディナリティと参照整合(外部キー)の間には関連はないので注意が必要である。
カーディナリティが1対Nであるなら、N側に1側の主キーを外部キーとしてもたせなくてはならない、というわけではない。ここではN側に1側の主キーを外部キーとして保持しているが、この後説明する３つ目のデータベース設計例ではその逆のパターンも出てくる。参照する側がもつ、と考えれおけばよい。


商品
　商品ID　商品名　定価　仕入れ価格　業者ID*
 　※商品名が重複する可能性があるため商品IDを付与している。

売上
　売上ID　日時　商品ID*　個数
 　※1つの売上には1つの商品しか扱わないとしている。
 　※売上IDがないと、例えば返品などの操作に対応できない。

業者
　業者ID　社名　住所　連絡先
 　※社名が重複する可能性があるため社名IDを付与している。


● 関連(relationship)とカーディナリティ(cardinarity)を記述し、ER図もどきを完成させる

商品 1 ― N 売上
 N
 |
 1
業者

1つの売上に対して複数の商品が売れることを想定した場合は商品と売上の関係は、N:Mになる。
レベル1なのでこのへんで。
次の例題ではDDLまで作成してみる。



【ブログシステム用データベース】 レベル2
● 実体を決め、そこに属性を追加する
ブログ
　ユーザID　ユーザ名　パスワード　ブログ名　URL　メールアドレス
　※ユーザは１つしかブログが作れないという条件があるサービスとしている。

記事
　記事ID　タイトル　記事本文　登録日時　カテゴリID　ユーザ名ID*

コメント
　コメントID　コメント本文　登録者　メールアドレス　登録日時　記事ID*

添付ファイル
　添付ファイルID　添付ファイル　記事ID*

カテゴリ
　カテゴリID　カテゴリ名　記事ID*

選択(記事、カテゴリ)
　記事ID*　カテゴリID*
　※記事とカテゴリは多対多関係にあるので中間(交差)テーブルを作成した。


● 関連とカーディナリティを記述し、ER図もどきを完成させる

ブログ       
  1
  |
  N   1 ― N  添付ファイル
 記事  1 ― N  コメント
      1 ― N  選択  M - 1  カテゴリ


● DDL(Data Definition Language:データ定義言語)もどきの作成
CREATE TABLE ブログ {
 ユーザID INT NOT NULL AUTO_INCREMENT, 
 ユーザ名 VARCHAR(32) NOT NULL,
 パスワード VARCHAR(32) NOT NULL,
 ブログ名 VARCHAR(32) NOT NULL,
 URL VARCHAE(64) NOT NULL,
 メールアドレス VARCHAR(128) NUT NULL,
 PRIMARY KEY(ユーザID)
};

CREATE TABLE 記事 {
 記事ID INT NOT NULL AUTO_INCREMENT, 
 タイトル VARCHAR(128) NOT NULL,
 記事本文 TEXT,
 登録日時 TIMESTAMP NOT NULL,
 ユーザID INT NOT NULL,
 カテゴリID NOT NUL,
 PRIMARY KEY(記事ID),
 FOREIGN KEY(ユーザID) REFERENCES ブログ(ユーザID),
 FOREIGN KEY(カテゴリID) REFERENCES カテゴリ(カテゴリID))
};

CREATE TABLE コメント {
 コメントID INT NOT NULL AUTO_INCREMENT, 
 コメント本文 VARCHAR NOT NULL,
 登録者 VARCHAR(32) NOT NULL,
 メールアドレス VARCHAR(128) NOT NULL,
 登録日時 TIMESTAMP NOT NULL,
 記事ID INT NOT NUL,
 PRIMARY KEY(コメントID),
 FOREIGN KEY(記事ID) REFERENCES 記事(記事ID)
};

CREATE TABLE 添付ファイル {
 添付ファイルID INT NOT NULL AUTO_INCREMENT 
 添付ファイル VARCHAR NOT NULL,
 記事ID INT NOT NUL,
 PRIMARY KEY(添付ファイルID),
 FOREIGN KEY(記事ID) REFERENCES 記事(記事ID)
};

CREATE TABLE 選択 {
 記事ID INT NOT NULL, 
 カテゴリID INT NOT NULL,
 PRIMARY KEY(記事ID, カテゴリID)
 FOREIGN KEY(記事ID) REFERENCES 記事(記事ID),
 FOREIGN KEY(カテゴリID) REFERENCES カテゴリ(カテゴリID),
};

CREATE TABLE カテゴリ {
 カテゴリID INT NOT NULL AUTO_INCREMENT, 
 カテゴリ名 VAR CHAR(32),
 記事ID INT NOT NUL,
 PRIMARY KEY(カテゴリID),
 FOREIGN KEY(記事ID) REFERENCES 記事(記事ID),
};



【オンラインショップ用データベース】 レベル3
● 実体を決め、そこに属性を追加する

顧客
　顧客ID　メールアドレス 顧客名　パスワード　住所　連絡先

商品
　商品ID　メーカ　分類 商品名　型番　定価　販売価格　在庫数
　※分類の作成も別出して作ってもいいかもしれない。

購入(案1)
　顧客ID*　商品ID*　個数　売値　日時
　※購入IDを導入せず、購入ID、商品IDの複合キーになっている。
　　ただしこれだと一回の購入で何を買い、いくら払ったのか分からない。
　　顧客からの問い合わせにも対応しにくい。
　　同じタイミングでの購入は同じ購入IDを振りたいだろう。

購入(案2)
　購入ID　顧客ID*　商品ID*　個数　売値　日時
  　   1      A       1    3  1000  2011/10/21 10:00:00
    　 1      A       2    2  5000  2011/10/21 10:00:00
　※購入ID、顧客ID、商品IDで複合主キーになっている。
　　１回の購入で同じ顧客が複数の商品を購入することもあるため、
　　購入IDだけでは、また購入ID+顧客IDの組み合わせでも一意に定まらないからである。

案3としてエンティティを購入と購入明細と分けてもいいかもしれない。
購入(案3)
　購入ID　顧客ID*

購入明細
　購入ID*　商品ID*　個数　売値　日時

購入(案3)は購入(案2)の正規化から作られたものではない。
購入(案2)の時点で十分に正規化はされている。

設計を購入(案2)にするか、購入(案3)にするか、さらに別の形にするかは、
何を実体ととらえるかという思想の違い、
またはデータベースの使い方によって変わってくる。

今回は購入(案3)を使って残りの設計を続けていく。


● 関連とカーディナリティを記述し、ER図もどきを完成させる

購入 N ― 1 顧客
 1
 |
 N
購入明細 1 ― N 商品


● DDL(Data Definition Language:データ定義言語)もどきの作成
CREATE TABLE 顧客 {
 顧客ID INT NOT NULL AUTO_INCREMENT,
 メールアドレス VARCHAR(128) NOT NULL,
 顧客名 VARCHAR(128) NOT NULL,
 パスワード VARCHAR(32) NOT NULL,
 住所 VARCHAR(32) NOT NULL,
 連絡先 VARCHAE(64) NOT NULL,
 PRIMARY KEY(顧客ID)
};

CREATE TABLE 商品 {
 商品ID INT NOT NULL AUTO_INCREMENT,
 メーカ VARCHAR NOT NULL,
 分類 VARCHAR NOT NULL,
 商品名 VARCHAR NOT NULL,
 型番 VARCHAR NOT NULL,
 定価 INT NOT NULL CHECK(定価 > 0),
 販売価格 INT NOT NULL CHECK(販売価格 > 0),
 在庫数 INT NOT NULL  CHECK(販売価格 >= 0),
};

CREATE TABLE 購入 {
 購入ID INT NOT AUTO_INCREMENT,
 顧客ID VARCHAR(128) NOT NUL
 日時 TIMESTAMP NOT NULL
 PRIMARY KEY(購入ID)
 FOREIGN KEY(顧客ID) REFERENCES 顧客(顧客ID)
};

CREATE TABLE 購入明細 {
 購入ID INT NOT NULL, 
 商品ID INT NOT NULL,
 個数 INT NOT NULL,
 PRIMARY KEY(購入ID, 商品ID)
 FOREIGN KEY(購入ID) REFERENCES 購入(購入ID),
 FOREIGN KEY(商品ID) REFERENCES 商品(商品ID),
};


あとはデータがあれば、アプリケーションからバシバシと操作できる。
SQLならではの購入ランキング検索なども簡単である。

SELECT 商品ID, sum(個数) AS 合計個数 FROM 購入明細
GROUP BY 商品ID ORDER BY 合計個数 DESC LITMI 10;

◆ インデックスの付与

インデックスの付与も検討材料だろう。

● メリット、デメリット

メリット

・検索がはやくなる

デメリット

・挿入、更新処理が遅くなる

・容量が増える

主キー制約、UNIQUE制約をつけた列に自動でインデックスがはられるデータベースは

ここへの手動での対応は不要である。

● どこにインデックスをはるか

最後の例【オンラインショップ用データベース】を使って説明する。

SELECT時にWHERE句の検索キーとなるところを対象とすればよい。

更新処理時でもUPDATEの後にWHERE句による条件があればそこも対象として検討した方がいい。

下の三つの検索が多いとする。

① 商品検索

SELECT * FROM 商品

WHERE メーカ = '*****' AND 分類 = '*****';

② 顧客購入履歴検索

SELECT 商品名, 個数 FROM 購入, 購入明細

WHERE 購入.購入ID = 購入明細.購入ID

AND 購入.顧客ID = '*****';

それぞれにインデックスをはってみる。

①

CREATE INDEX 商品_メーカ_index ON 商品(メーカ);

CREATE INDEX 商品_分類_index ON 商品(分類);

一つのクエリから発行されている場合は複合インデックスを作成できる。

CREATE INDEX 商品_メーカ_分類_index ON 商品(メーカ, 分類);

②

CREATE INDEX 購入_顧客ID_index ON 購入(顧客ID);
CREATE INDEX 購入明細_購入ID_index ON 購入明細(顧客ID);

次は、sql select文のスキルアップ編へ