WorkBenchでDB(ER図)を初めて作ってみた〜〜


#概要
まっさらなローカルにMySQLのDBを作成してみましたー！
勉強中のブログ、ニュース投稿機能付きのWebサイトに必要なデータになります。

#先に成果物
ブログ、ニュース機能付きのWebサイトに必要なDBです！
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/314481/097d4c22-a469-3c1c-109b-eeab7d802ddc.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/314481/0a1c7b0c-2596-a117-6695-06410ad098a4.png)

#ステップ
1. エンティティーの抽出（データの集合体の選定）
2. エンティティーの定義（テーブル定義）
3. WorkBenchでローカルでDB作成
4. ER図で確認

まずは手を動かしたいけど、ぐちゃぐちゃになってしまうので、準備８割手を動かすのを２割にしていきますー！

#前提
- MySQLがDLされていること（必須）
- WorkbenchがDLされていること（必須）
- 想定しているアプリのユーザーストーリや必要画面がまとめてある（あれば良）
- 必要なAPIがわかっている（あれば良）

#ステップ1. エンティティーの抽出（データの集合体の選定）

エンティティーの抽出とは？

**「今回使うデータを持ってこよう」**

ってことです。

**開発に必要なデータの抜けの無いように**作成します。
今回の場合だと、

- 記事（ブログ・ニュース）
- アカウント(管理者)

はい、これでステップ１終了です！

意外と早かったですね ^○^/


#ステップ2. エンティティーの定義（テーブル定義）

どんなテーブルがどんな情報を持つか決めるステップです。



今回は必要なAPIをまとめたのでそこから逆算してテーブル定義していきます。

|API|説明|
|:-:|:-:|
|CRUD article| 記事のCRUD|
|CRUD account|アカウントのCRUD|

今回は、ニュースとブログ別表示だけど同じテーブルで作成しました。

##まずはテーブルの構造を決めた
###記事（ニュースとブログ）
記事テーブルのIDをリレーションテーブルでタイプ分別して、記事タイプテーブルのnameカラムにニュースかブログが入るということです。

縦に１テーブルずつ並んでいて、カラムがあるようにみてください。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/314481/aa5116c0-1b62-1222-adf1-95a211834b8f.png)

###アカウント（記事とアカウント）
先ほど作成した記事テーブルにアカウントテーブルを関連づけています。
誰が記事を投稿したかわかるようにするためと、管理者としてログイン機能もつけるために、電話番号やパスワードなども保存できるようにしています。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/314481/6850293f-cc36-9e99-99d6-a69f9f123331.png)

##データの型とタイプを決めた

[型ってなに？](https://programmingnavi.com/695/)

###先ほどの記事（ニュースとブログ）で紹介したテーブルに型とタイプを決めました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/314481/6a55046e-5a80-865b-99da-e0ac63143b09.png)

###同様にアカウント（記事とアカウント）も行いました
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/314481/32313831-915d-6fe2-0855-24ef685a327c.png)

サクッとしすぎてわからなかったら[こちら](https://qiita.com/nishina555/items/a79ece1b54faf7240fac#%E3%82%B9%E3%83%86%E3%83%83%E3%83%971-%E3%82%A8%E3%83%B3%E3%83%86%E3%82%A3%E3%83%86%E3%82%A3%E3%81%AE%E6%8A%BD%E5%87%BA
)を参考してみるといいかもです 
#ステップ3.WorkBenchでローカルでDB作成

かなり[このURLを参考にさせていただきました。](https://qiita.com/hiesiea/items/01dde88d8062c3d16910)

##とにかくDBを作ってみる

まずは[参考記事](https://qiita.com/hiesiea/items/01dde88d8062c3d16910)に目を通しましょう！


その中でリレーションを貼るところがなかったので紹介します。いわゆる外部キーですね！

あと、リレーションテーブルがある場合のテーブルの作成順序も気をつけなくてはなりません。


###必要だった知識① primaryとuniqueとkeyについて

>PRIMARY KEY は、すべてのキーカラムを NOT NULL として定義する必要のある一意のインデックスです。それらが NOT NULL として明示的に宣言されていない場合、MySQL は、それらを暗黙的に (かつ警告なしで) そのように宣言します。テーブルに存在できる PRIMARY KEY は 1 つだけです。PRIMARY KEY の名前は、常に PRIMARY です。そのため、これをその他のどの種類のインデックスの名前としても使用できません。

>KEY は通常、INDEX のシノニムです。キー属性 PRIMARY KEY もまた、カラム定義内で指定する場合は、単に KEY として指定できます。これは、ほかのデータベースシステムとの互換性のために実装されました。

>UNIQUE インデックスは、そのインデックス内のすべての値が異なっている必要があるという制約を作成します。既存の行に一致するキー値を持つ新しい行を追加しようとすると、エラーが発生します。すべてのエンジンについて、UNIQUE インデックスは、NULL を含むことができるカラムでの複数の NULL 値を許可します。

リファレンス　https://dev.mysql.com/doc/refman/5.6/ja/create-table.html


よーするに

|primary|unique|key|
|:-:|:-:|:-:|
|テーブルに一つしかない|そのカラムで被らないようにする|index貼るため

###必要だった知識②外部キー制約の設定方法

[リファレンス](https://dev.mysql.com/doc/refman/5.6/ja/create-table-foreign-keys.html)によると
`CONSTRAINT`のなかに`FOREIGN KEY`や`REFERENCE`などを用いて作成しています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/314481/910ba03a-09fd-6683-52af-2dca85f650a3.png)

なので、リレーションテーブルの方には`CONSTRAINT`を用いて外部キーを設定していこうと思います。

###必要だった知識③テーブルを作る順番

今回はリレーションテーブルがあるので、**先に作成しなくてはならないのは親のテーブル**です。

なので合計作成する５つのテーブルのうち

- 記事テーブル
- 記事タイプテーブル
- アカウントテーブル
- 記事タイプリレーションテーブル
- 記事ライターリレーションテーブル

先に３つのテーブルから作成していきます！

- 記事テーブル
- 記事タイプテーブル
- アカウントテーブル


親テーブルがない状態では子テーブルは作成できませんからね！


###先に３つの親テーブルから作成

####記事テーブル

workbenchのボタンとかで簡単に作れるし、MySQLのcreate文を使ってもどっちでも作れますが、一応勉強がてらcreate文を使用しました。

```
CREATE TABLE `articles` (
  `id` bigint(18) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `title` varchar(150) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'タイトル',
  `description` mediumtext COLLATE utf8mb4_general_ci COMMENT 'ディスクリプション',
  `thumbnail_url` varchar(255) COLLATE utf8mb4_general_ci DEFAULT '' COMMENT 'サムネイルURL',
  `social_thumbnail_url` varchar(255) COLLATE utf8mb4_general_ci DEFAULT '' COMMENT 'SNS用サムネイルURL',
  `content` mediumtext COLLATE utf8mb4_general_ci COMMENT '記事本文',
  `page_view` int(100) NOT NULL DEFAULT '0' COMMENT 'ページビュー数',
  `is_publish` tinyint(4) NOT NULL DEFAULT '0' COMMENT '公開フラグ(0=非公開,1=公開)',
  `publish_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '公開日時',
  `enable` tinyint(1) NOT NULL DEFAULT '1' COMMENT '有効フラグ(0=無効,1=有効)',
  `created_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '作成日時',
  `updated_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新日時',
  `deleted_date` datetime DEFAULT NULL COMMENT '削除日時',
  PRIMARY KEY (`id`),
  UNIQUE KEY `title` (`title`),
  KEY `publish_flg` (`is_publish`),
  KEY `enable` (`enable`),
  KEY `created_date` (`created_date`),
  KEY `publish_date` (`publish_date`)
);
```
こんな感じで残りのアカウントテーブルと記事タイプテーブルも作成します。

####記事タイプテーブル

```
CREATE TABLE `article_types` (
  `id` bigint(18) unsigned NOT NULL AUTO_INCREMENT COMMENT 'アカウントID',
  `name` varchar(45) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'タイプ',
  `created_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '作成日',
  `remark` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '備考',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_UNIQUE` (`name`)
)
```

####アカウントテーブル
```
CREATE TABLE `accounts` (
  `id` bigint(18) unsigned NOT NULL COMMENT 'アカウントID',
  `account_key` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'account key',
  `enable` tinyint(1) NOT NULL DEFAULT '1' COMMENT '有効フラグ(Boolean)',
  `first_name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '名前（母国語）',
  `last_name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '苗字（母国語）',
  `first_name_kana` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '名前（カナ）',
  `last_name_kana` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '苗字（カナ）',
  `email` varchar(100) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'email',
  `email_hash` varchar(64) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'email_hash',
  `password_hash` varchar(255) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'email_password_hash',
  `tel_number` varchar(20) COLLATE utf8mb4_general_ci NOT NULL COMMENT '電話番号',
  `created_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '作成日時',
  `updated_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新日時',
  `deleted_date` datetime DEFAULT NULL COMMENT '削除日時',
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `tel_number_UNIQUE` (`tel_number`),
  UNIQUE KEY `account_key_UNIQUE` (`account_key`)
);
```

以上で3つのテーブルを作成しました。

###次に2つのリレーション、子テーブルを作成

####記事タイプリレーションテーブル
```
CREATE TABLE `article_type_relations` (
  `article_id` bigint(18) unsigned NOT NULL COMMENT '記事ID',
  `article_type_id` bigint(18) unsigned NOT NULL COMMENT '記事タイプID',
  `created_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '作成日',
  `updated_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新日',
  UNIQUE KEY `article_id_article_type_id_UNIQUE` (`article_id`,`article_type_id`),
  KEY `fkey_article_types_article_type_id` (`article_type_id`),
  CONSTRAINT `fkey_article_types_article_id` FOREIGN KEY (`article_id`) REFERENCES `articles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fkey_article_types_article_type_id` FOREIGN KEY (`article_type_id`) REFERENCES `article_types` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
);
```
####記事ライターリレーションテーブル
```
CREATE TABLE `article_writer_relations` (
  `article_id` bigint(18) unsigned NOT NULL COMMENT '記事ID',
  `account_id` bigint(18) unsigned NOT NULL COMMENT 'アカウントID',
  PRIMARY KEY (`article_id`,`account_id`),
  KEY `article_id` (`article_id`),
  KEY `fkey_article_account_relations_ibfk_2` (`account_id`),
  CONSTRAINT `fkey_article_account_relations_ibfk_1` FOREIGN KEY (`article_id`) REFERENCES `articles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fkey_article_account_relations_ibfk_2` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
);
```

これで必要なDBを作成することができました！最後にER図を確認してみましょう！

#ステップ４.ER図を確認

WorkbenchでER図を出力する方法は上のタブから

`Database >reverse engineer > continue > checkbox > continue > Execute`

でみることができます。

するとどうでしょうか？
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/314481/097d4c22-a469-3c1c-109b-eeab7d802ddc.png)

できましたね！！

お疲れ様でした！！

##参考
- https://programmingnavi.com/695/
- https://qiita.com/nishina555/items/a79ece1b54faf7240fac#%E3%82%B9%E3%83%86%E3%83%83%E3%83%971-%E3%82%A8%E3%83%B3%E3%83%86%E3%82%A3%E3%83%86%E3%82%A3%E3%81%AE%E6%8A%BD%E5%87%BA
- https://qiita.com/hiesiea/items/01dde88d8062c3d16910
- https://dev.mysql.com/doc/refman/5.6/ja/create-table.html