More than 5 years have passed since last update.

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

Last updated at 2020-06-12Posted at 2019-11-22

概要

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

先に成果物

ブログ、ニュース機能付きのWebサイトに必要なDBです！

ステップ

エンティティーの抽出（データの集合体の選定）
エンティティーの定義（テーブル定義）
WorkBenchでローカルでDB作成
ER図で確認

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

前提

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

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

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

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

ってことです。

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

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

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

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

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

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

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

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

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

まずはテーブルの構造を決めた

記事（ニュースとブログ）

記事テーブルのIDをリレーションテーブルでタイプ分別して、記事タイプテーブルのnameカラムにニュースかブログが入るということです。

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

アカウント（記事とアカウント）

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

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

型ってなに？

先ほどの記事（ニュースとブログ）で紹介したテーブルに型とタイプを決めました。

同様にアカウント（記事とアカウント）も行いました

サクッとしすぎてわからなかったらこちらを参考してみるといいかもです

ステップ3.WorkBenchでローカルでDB作成

かなりこのURLを参考にさせていただきました。

とにかくDBを作ってみる

まずは参考記事に目を通しましょう！

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

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

必要だった知識① 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貼るため

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

リファレンスによると
CONSTRAINTのなかにFOREIGN KEYやREFERENCEなどを用いて作成しています。

なので、リレーションテーブルの方には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

でみることができます。

するとどうでしょうか？

できましたね！！

お疲れ様でした！！

参考

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up