この記事を読む前に
誰のために記事にするのか.
⇨データベースをまだ学んで日が浅い未経験者のエンジニアを目指している方.
何を記事にするのか.
⇨データベースと正規化の目的のおさらいとデータベースの正規化の欠点と非正規化について,正規化がもたらすデメリットと非正規化のメリット.
目次
①データベースと正規化の目的.
②正規化と検索SQLのパフォーマンスはトレードオフの関係.
③まとめ.
①データベース、正規化の目的
データベースの目的.
⇨たくさんのデータを保管すること。それは後からデータを活用するために保存している.
注意データベース内には間違ったデータが保存されてはいけない.
正規化の目的は
データの整合性を保つこと.
データの整合性を保つとはデータベース内に間違ったデータが管理されないようにする、またデータベースに間違ったデータが保存されないようにするということ.
間違ったデータとは.
⇨データを保存する際ルールがありルールに従わずに保存されたデータは間違ったデータである。
ルールとはデータベース内のデータは一意に特定できないといけない。
例えば
顧客テーブルがあったとして
↑のようなテーブルはデータベース内に保管できない。要は間違ったデータが保管されていることになる。
どこが間違っているかというと顧客IDの中に2つ同じ値がある=データを一意に特定できない.
↑の画像の例でいくと顧客IDが1の値が2つある。これだとデータベースから情報を取得する際に田中か佐藤のどちらの情報を取得すればいいのかPCにはわからない.なのでこの場合顧客IDが1だと田中、2だと佐藤のように顧客IDを指定すると必ず特定の値を示すようにしないといけない.
つまり正規化はデータベースが正しくデータを保存ための方法として正規化がある.
②正規化と検索SQLのパフォーマンスはトレードオフの関係
⇨トレードオフとは「両立できない関係性」を示す言葉として使われています。 言い換えると、一方を尊重すればもう一方が成り立たない状態のこと。
例えば
格闘技で言うと体重を増やすほど力は強くなるが動くスピードが落ちる、逆に体重を減らすほど力は弱くなるが動くスピードが上がる.
つまり力を強くしスピードを上げるのは難しい.
正規化と検索SQLのパフォーマンスも格闘技と同じ関係である.
正規化してデータの整合性を高めれば高めるほど検索SQLの処理に時間がかかり、重複している値を放置(非正規化)しているとデータの整合性が保ちにくくなる
が検索SQLの処理の時間が短くなる.
ここでの私の疑問.
なんで正規化したらSQLでデータを取得するスピードは遅くなるん?正規化するとデータの重複(無駄)がなくなりデータベースからデータの取得スピードは上がりそうなのに.
答えは正規化したテーブルからSQLを使ってデータを取得する際『内部結合or外部結合』を行なってからデータを取得しているため.
内部結合と外部結合とは要は複数のテーブルを1つにまとめてから情報を取ってくること.
内部結合と外部結合はSQLで行うわけだがこの処理はSQLからするとすごいコストのかかる作業であるがために処理時間がかかる。特にテーブル数、テーブルのレコード数が増えれば増えるほど処理時間がかかる.
例えば、会社、社員、部署といった3つのテーブルがあったとする
このデータベースから、田島さんが勤めている会社を知りたいとする.
会社
会社コード | 会社名 |
---|---|
C0001 | A商事 |
C0002 | B化学 |
C0003 | C建設 |
社員
会社コード | 社員ID | 社員名 | 年齢 | 部署 |
---|---|---|---|---|
C0001 | 000A | 加藤 | 40 | D01 |
C0001 | 000B | 藤本 | 32 | D02 |
C0001 | 001F | 三島 | 47 | D03 |
C0002 | 000A | 斎藤 | 47 | D03 |
C0002 | 009F | 田島 | 25 | D01 |
C0002 | 010A | 渋谷 | 33 | D04 |
部署
部署コード | 部署名 |
---|---|
D01 | 開発 |
D02 | 人事 |
D03 | 営業 |
D04 | 総務 |
SQLを使ってデータベースから田島さんが勤めている会社を取得する際.
データベースは会社テーブルを見ても会社はわかるけど田島さんがどの会社に勤めているかわからない、社員テーブルを見ても田島さんはいるがどこの会社に勤めているかはわからない。ただ外部キー(会社コード)を社員テーブルは持っていると認識する.そこでデータベースは田島さんと田島さんが勤める会社が1つのテーブルに存在すれば田島さんの勤めている会社わかるよーとなる。つまり正規化して分割したテーブルを元の1つのテーブルに戻せばデータベースから欲しい情報が手に入る。そこで複数のテーブルを1つに戻すことを結合といい。内部結合と外部結合の2種類ある.
今回は内部結合で取得する.
それぞれの使い分けは後ほど紹介する.
SELECT 会社.会社名 , 社員.社員名 FROM 社員 INNER JOIN 会社 ON 社員.社員コード = 会社.会社コード WHERE 社員.社員コード = '田島';
会社名 社員名 ----- ----- B化学 田島
次は会社ごとに社員数の数が知りたいとする.
SELECT 会社.会社コード , COUNT(社員.社員名) AS 社員数 FROM 会社 LEFT OUTER JOIN 社員 ON 社員.会社コード = 会社.会社コード GROUP BY 会社.会社コード
ここでは外部結合を使用する.
会社コード 社員数 ----- ----- C0001 3 C0002 3 C0003 0
内部結合と外部結合の使い分け方.
⇨取得したいデータによって使い分ける.
例えば.
先ほど説明した会社ごとに社員数の数が知りたいといった時に内部結合を使ってデータを取得すると
会社コード 社員数 ----- ----- C0001 3 C0002 3
このような結果が返ってくる。
内部結合はテーブルを結合する際「両方のテーブルに共通するものを取得する」
ここで上の社員テーブルと会社テーブルを見比べてみるとC0001
C0002が両方のテーブルに存在するのでこの2つの会社それぞれの社員数を表示することになる.
外部結合は「どちらか一方のテーブルに値が存在していれば値を表示する」
C0003 は会社テーブルにしか存在していないが結合するテーブルの片一方に存在していればいいのでc建設の会社の社員数が表示される.
つまり
内部結合は両方に共通する値なので=and
外部結合はどちらか一方にあればいいので=orと覚えればわかりやすい
内部結合のinnerのnはandのこと.
外部結合の外は英語でoutでoはorのこと.
まとめ
非正規化にするか正規化するか迷いどころだが基本的に正規化する方が良い。非正規化は最後の手段である.
参考文献
達人に学ぶDB設計 徹底指南書