はじめに
先日Youtubeでプログラミング関連の動画をあさっていたところ、かなりわかりやすくSQLを解説していた動画があったのでそれを記事にまとめようと思いました。去年データベースの授業をとっていたものの、十分な理解を得られなかったので戒めとして記事を書きたいと思います。データベース自体は初心です。
あくまで備忘録なのではじめに以下の動画を見てきた方がいいです。この記事の元になっているので、ぜひ動画を再生してきてください。
・【2021年版】データベース＋SQL入門｜MySQL/PostgreSQL/Oracleなどデータベースの使い方や役割・SQLについて👉初心者向けに6分で解説
・データベース設計入門#1 リレーションとER図【11分でマスター！DB設計】
・データベース設計入門#2 正規化｜無駄のないテーブル設計とは？【日本一わかりやすくDB正規化を解説します】
データベースとは
簡単な説明
まず商品を検索する際の流れについて
1. Web上で商品名検索
2. Webサーバー上でPHPに処理を依頼する
3. PHPはデータベースで商品名該当するものの一覧をとってきて、それをユーザーにレスポンスとして返す。
【用語説明】
Webサーバー...パソコンやスマートフォンなどの端末からHTTP/HTTPSで送られたリクエストに対してHTMLK、CSS、JavaScriptなどの情報を返す
例) Apache、Nginx
PHP...動的にWebページを生成することができるサーバーサイドのスクリプト言語
ここら辺の記事もいつか書きたいなぁとか思います。大学の授業でやってくれないのかな...
この検索や蓄積が容易にできるよう整理された情報の集合のことをデータベース(以下DB)と呼びます。商品カタログみたいなものですね。その中でもよくつかわれるのがRDBと呼ばれるもので、MySQLとかPostgreSQL、Oracleが該当します。データを複数の表として管理し、表と表の関係を定義することで、複雑なデータの関連性を扱えるようにしたデータベース管理方式のことです。。他にも階層型、NoSQL、ネットワーク型と様々な種類があって、DBで有名なMongoDBはNoSQL型、SQLiteはRDB型となっています。
テーブル
【用語説明】
テーブル...種類ごとにデータを管理する場所
カラム...列
レコード... 行
SQL...DBを操作するための言語
主キーと外部キーについて
主キー...ある値を指定すると特定のタプルを一意指識別できる属性をもつ列のこと。
外部キー...関連したテーブル間を結ぶために設定された列のこと。
子テーブル...外部キーを設定して参照する側。
親テーブル...外部キーの設定元。
上の図の従業員テーブル(子テーブル)おいて、従業員番号がわかれば部署番号や姓、名が一意に決まるので従業員番号は主キーになります。部署番号や姓、名がわかっても他の属性は一意に決まらないので主キーにはなりません(例えば、姓において「侍」がわかっても複数名姓が「侍」の人がいるので部署番号や名は特定できません)。そして、部署番号について、他のテーブル(部署テーブル)と関連付けられているため、これは外部キーとなります。
【SQL入門】外部キーとは？主キーとの関係や作成方法について解説
リレーションについて
IE記法(Information Engineering Notation)...〇、|、鳥の足(3つ股の線)の記号を組み合わせて表現された記法
ER図(Entity-Relationship Diagram)...データベース内のテーブル同士の関係性をあらわした図
参考: 若手プログラマー必読！５分で理解できるER図の書き方５ステップ
主に4つの関係があることを把握すれば十分です。0が対になっているER図はそのテーブルが存在していない。上の例だと、商品マスタになにも商品が入ってないことを示しています。
他に用語だとエンティティ(データのまとまり)、アトリビュート(エンティティの中の属性情報)、リレーション(エンティティ同士の関係を表現する線)、カーディナリティ(「１対１」「１対多」「多対多」など、リレーションの詳細を表現する記号のこと)というものがあります。英語でそれぞれentity、attribute、relation、cardinalityとなっているのでそちらの方思い出せれば特に困りませんね。
エンティティの種類
エンティティはマスタ系とトランザクション系に分かれています。
マスタ系...企業内データベースなどで、業務を遂行する際の基礎情報となるデータ
トランザクション系...企業の情報システムなどが扱うデータの種類の一つで、業務に伴って発生した
出来事の詳細を記録したデータ
IT用語辞典から引っ張ってきましたがよくわからなかったため、調べたところ、マスタ系は従業員や部署、役職のように流動的に処理内容が更新されないものに対して、トランザクション系は流動的に追加・更新が行われるデータのことを示すそうです。
Amazonで買い物をすると、届け先とか決済方法とか表示されると思いますが、そうした毎回更新の手続きが必要になるものはトランザクション系で、商品情報や顧客の住所、電話番号のように毎回更新しなくてもいいものがマスタ系の解釈でいいと思います。
マスタとトランザクション
ER図をつくるためのツール
・Cacoo...https://cacoo.com/ja/
・MySQL Workbench...既存のテーブルからER図を自動的に作成https://www.mysql.com/jp/products/workbench/
・drawio...大学の授業で使ってましたhttps://app.diagrams.net/
実際にDBを作ってみる(ER図)
ここからはER図を用いてDBの外観を掴んでいこうと思います。
SQLアンチパターン
DBを作成するにあたってしてはいけないパターンがあります。
例えば
こういうER図があったとします。社員テーブルにサークルidの外部キーを、サークルテーブルに社員idの外部キーを持たしていますがこれはDBのよくない設計方法とされています。というのも、それぞれサークルが二つ、社員が二人までしかテーブルが持つことができないからです(=拡張できない)。特に多対多でよく陥りやすいアンチパターンなので気を付ける必要があります。解決策としては以下のように中間テーブルを社員とサークルテーブルの間に入れます。
大学でここまで丁寧な説明とか、SQLアンチパターンの概念など教えてくれなかったので悔やまれるところありますね...
いかにアンチパターンがまとまって掲載されている書籍を置いておきます。データベース関連に将来携わるなら読むべきなのかな、
正規化
例えば上記のようなデータがあるとして、これをSQLが読める形にするには？という疑問から登場してきたのが正規化という概念です。この記事の参考動画でわかりやすく説明されてるので簡潔に書いていきます。
データベース設計入門#2 正規化｜無駄のないテーブル設計とは？【日本一わかりやすくDB正規化を解説します】
正規化の長所
1.データが整理されることで、他システムとの連携や移行などが行いやすくなる
2.無駄な重複が削除されることにより、保存に必要なデータ領域の削減につながる
3.同じデータが何度も登場しないようにするため、変更があった場合の修正が容易になる
第一正規化
まず、横に伸びている繰り返しを縦に伸びるように並び替えます。上記図において青いコラムのところ(商品名、単価、数量)は、複数回繰り返されているのでそれらを分割して縦に結合していきます。
第二正規化
次に、縦に伸びている繰り返しを分割して、新しいテーブルを作成します。先ほどの図では明細が繰り返されるたびに、ユーザ名や発送先住所といった流動性のないデータ(マスタ系でいいのかな？)が繰り返されています。そのため、繰り返しのないデータ(購入履歴本体、マスタ系?)と、繰り返しのあるデータ(購入履歴明細、トランザクション系?)を分割して、それぞれ違うテーブルにします。ここで、テーブル間の関連性を残しておくために、注文番号を外部キーとして設定しておきます(注文番号は主キーでもあり、外部キーでもあります)。
第二正規化を行った後は上のような図になります。流動的データと非流動的データをうまく分割できました。
動画内では主に横と縦の冗長をなくすという方向性で説明がなされていましたが、第二正規化では「従属関係にあるデータの分離」を行うことが基本です。そのため、上の図において例えばユーザIDがわかればユーザ名、発送先住所、送料区分も基本的に一意に決まるのでユーザIDのカラムを追加し、新しくユーザデータテーブルを作ることにします。また、商品番号がわかれば商品名や単価も一意に決まるので商品番号のIDを追加し、新しく商品データテーブルを作ることにします。
※従属関係...ある属性Xを決めると、他の属性Yの値が一意に決まる関係の事
第三正規化
推移的関数従属と呼ばれる従属関係を分離します。上記の例で推移的関数従属とはユーザデータにおいて、ユーザーIDがわかれば発送先住所がわかって、発送先住所がわかれば送料区分もわかるが、ユーザーIDがわかっても送料区分はわからない(送料区分は発送先住所によって決定され、ユーザーIDによっては決定されないため)という３段論法できな関係のことをいいます。第三正規化を行った後の図は以下のようになります。
おそらく実務上では送料区分に対応する料金情報などを付加したいときにここまで細分化されていれば変更が楽になるよね...というニュアンスだと思います。(ユーザーIDと送料区分自体も推移的関数従属のような気がしますが、おそらく本質は送料区分コードがわかれば送料区分名称と送料が一意に決まって、データの汎用性が高くなることだと思います。)
ボイスコッド正規化
主キー以外のカラムが全て主キーに完全関数従属であり、それ以外の従属関係があれば表を分離する。
※完全関数従属一方の値が決まると他の項目の値も一意に決まる関係
上記の図の上段の図において、ユーザデータテーブルではユーザ名とメールアドレスは登録時に固定されたりするため、変更のない完全に非流動的なデータとなります(メールアドレスを変更する場合もありますが、基本的にはメールアドレスが変更されるようなことはありません)。一方、ユーザ名と発送先住所の場合、発送先住所は基本的に非流動的なデータですが、例えば実家に商品を送ろうとするときとかは発送先住所がその時に変わってしまうので一時的に流動的なデータとなります。つまり、ユーザ名とメールアドレスのような一対一で紐づけられているデータは別のテーブルにしまっておこうとするのがこの正規化です。
正規化については第四、第五...と続きますが、実務上では第三正規化までらしいので説明はここまでにしておきます。
実際のデータベースの作成手順
今回の記事を書こうとしたきっかけとなる分野です。動画を見ててすごく興味深かったです。
システムの要件と機能を明確にする
動画に則って、新しくAmazonのようなECサイトを作る(=システムの要件)とします。
機能一覧は次のようなものが考えられます。
- フロント画面
- 商品検索
- 商品詳細ページ
- マイページ
- ログイン
- 購入履歴
- 管理者画面
- ログイン
- 商品管理
- 商品カテゴリー
これらをもとにざっとテーブルの一覧を洗い出します。実際にECサイトをどのように使っていくのかシナリオを考えると漏れができるだけなくなっていいらしいです。元動画がとても分かりやすいですはい。
テーブルの詳細設計
次にテーブルの詳細設計を決めます。以下の手順に従って進めます。
1.日本語を英語に対応させる
日本語の呼び名を論理名、英語の呼び名を物理名(DBで実際に扱う名前)と呼ぶらしいです。
また、予約語というものがあり、If, Null, Limit, Dateなどがあります。プログラミングでも、例えばmath.pyとかpandas.pyなどといった名前でpythonのファイルを作成すると、import pandasとかimport mathとかで本来呼び出したいものではなく、自分が作成した名前のファイルが呼び出されますよね...
2.カラムに型をつける
参考:データ型
Oracleなどでは別のデータ型もあるらしく、使うDBによって変える必要があると思います。
varcharの桁数が2, 4, 8, 16, 32, 64, 128, 255...のように2の累乗数を指定します。ただし例外的に256だけは255を指定するらしいです。
また、各テーブルににはid(主キー)とcreated_at、updated_atという三つのカラムを追加します。作成日時や更新日時を自動で入れ込むコードがあったほうが運用しやすいといったニュアンスです。
3.ER図を書きながら正規化する
最終形態になります。おもに関係性があるものには外部キーをもたせ、多対多の関係性(itemsとitem_categories)の場合は先述のアンチパターンを参考に中間テーブルitem_item_categoriesを作成し、一体多の関係性にします。
4.インデクスや制約条件ををつける
インデクス
インデクスとは対象レコードの格納位置を示すポインタのことです。例えば商品を探す際に、商品棚から一つずつ商品を取り出して表示させたい商品をさがすよりかは、インデクスを元に二分木を用いて高速に商品を探した方が時間が省けます。インデクスはこうした検索対象となりえるキーになるカラムにつけますが、主キーや外部キーは自動でつくことだけ念頭に置けばいいです。例えば今回の場合だと、itemsテーブルのname VARCHAR(255)は商品名検索するときに用いられるためインデクスを付けます。
レコード...あるテーブルに格納された、一組の値の重なりの事(行、組)
制約
制約は以下の三つに主に分かれます。
・NOT NULL制約...項目入る値がNULLでないことを保証する制約。基本的にすべてのカラムに対してつけます。
・ユニークキー制約...列あるいは列のグループに含まれるデータが、テーブル内のすべての行で一意であることを要求する制約。重複する可能性があるカラムの内容に対してつける。今回の場合だと、item_categoriesのname VARCHAR(255)とitemsのname VARCHAR(255)、shopsのname VARCHAR(255)が該当します。usersのname VARCHAR(255)は同姓同名がありえるのでユニークキー制約はつけません。
・外部キー制約...親テーブルと子テーブルの2つのテーブル間でデータの整合性を保つために設定される制約。外部キーに該当するものにすべて付けます。
終わりに
疲れました。また何かしらの記事を書こうと思います。データベース触る機会があれば続編か何か書こうともいます。