MySQL Workbench でデータモデルを作成してみる

データモデリングとはデータベースの設計図のようなものです
ざっくり言うと業務分析からデータベースの設計までの過程をモデル図で表現する手法です

モデリングとはどんなものか知るには、この記事が参考になります
30分間データモデリング~ER図を描こう! – IT

実際の業務を分析して、エンティティと言われる 誰が、何を、どうする というデータに抽出して、そのエンティティを過不足なく効率よく管理するための関連性を定義したデータモデル図を特定の記法により表現したものが、ERD(entity-relationship diagram)と言われます

エンティティという聞きなれない言葉が出てきますが、データベース構造から見るとエンティティとは、データテーブルに相当するものです

とりあえず概要がわかったところで Workbench を使って ERD を作成してみたいと思います

シンプルな例はないかと検索したら、下記記事が見つかりました
NetBeans E コマースのチュートリアル – データモデルの設計

この記事を参考にERDの作成手順を順に行ってみたいと思います

エンティティ関係図(ERD)の作成

MySQL Workbench を起動します

clip_image001

画面下部の Models のプラスマークをクリックします

clip_image002

ここから順番に下記作業を行っていきます

  1. スキーマの作成
  2. エンティティ(テーブル)の作成
  3. エンティティのプロパティを追加
  4. リレーションの作成
スキーマの作成

ここではまだ、データベースサーバー上にスキーマ(データベース構造)を作成するわけではありません
データモデリングするための設計図としてローカル上にスキーマを構築していきます

画面中央部の Physical Schemata の右側にあるプラスアイコン をクリックします

image

画面下部に新しいパネルが開くので、スキーマに次の設定を入力します

  • Name: affablebean
  • Collation: utf8 – utf8_unicode_ci
  • Comments: Schema used with the AffableBean application
エンティティ(テーブル)の作成

画面中央部の Physical Schemata の上にある Add Diagram をダブルクリック します
空のEER図(Enhanced Entity-Relationship)が表示されます

image

Diagram パネルの左に並んでいるアイコンから place a New Table アイコン をクリックして、キャンバス上にマウスを移動し再度クリックするとキャンバス上にテーブルが表示されます

image

表示された テーブルをダブルクリック すると画面下部にテーブルエディタが開きます
右上の 下向き矢印 をクリックすると Engine や comment を設定できるので、下記設定を入力します

  • Table Name: customer
  • Engine: InnoDB
  • Comments: maintains customer details

同様の手順を繰り返して、category, customer_order, product のテーブルを追加します

image

 

エンティティのプロパティを追加

エンティティのプロパティは、データベーステーブルの列(カラム)の定義に相当します
モデリングの初期設計段階は、エンティティとそのプロパティを決定することが主となります

プロパティの名前とデータ型を設定していきます
テーブルエディタを開き、テーブルの空欄部分をダブルクリック してカラムデータを設定します

Customer テーブルのプロパティに下記設定を行います

Column Name Datatype
id INT
name

VARCHAR(45)

email

VARCHAR(45)

phone

VARCHAR(45)

address

VARCHAR(45)

city_region

VARCHAR(2)

cc_number VARCHAR(19)
image

Primary Key や Not Null 等の設定も必要に応じて行います

同様に category, customer_order, product のプロパティも設定します

Category

id TINYINT PK,NN,UN,AI
name

VARCHAR(45)

NN

Customer_order

id INT PK,NN,UN,AI
amount DECIMAL(6,2) NN
date_created TIMESTAMP NN CURRENT_TIMESTAMP
confirmation_number INT NN,UN

product

id INT PK,NN,UN,AI
name

VARCHAR(45)

NN
price DECIMAL(5,2) NN
description TINYTEXT
last_update TIMESTAMP NN CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

image

 

リレーションの作成

作成したエンティティ(テーブル)が他のエンティティを参照する場合の定義を行います
参照の実態は、主キーを外部キーに関連付けることです

参照には、1方向, 双方向 があり、1対多 あるいは 多対多の関係を作成していきます
あまりイメージが湧かないかも知れませんが、GUI操作から簡単にリレーションを作成することができます

1対多の関係を作成

Category product の関係

カテゴリには1つまたは複数の製品が含まれる

Customer customer_order の関係

顧客は1つまたは複数の注文をする
最初に Category と product の関係(1対多の非依存関係)を設定します
  1. Diagram パネル左の place a New 1:n Non-Identifying Relationship アイコン をクリック
  2. product テーブル をクリック
  3. category テーブル をクリック
この3クリックだけで、product テーブルに category を参照するための category_id が 追加され、外部キーのインデックス fk_product_category がテーブルのインデックスに追加されます
外部キー category_id のデータ型は、category テーブルの主キーに合わせられ、TINYINT 型となります
image

relationship 関係(エンティティ間の破線)をダブルクリックすると画面下部に Relationship editor が表示されます

image

Caption を関係性を端的に表す文言として belongs to に変更します
Product belongs to category (製品xは、カテゴリーyに属している)

Forign Key タブをクリックするとリレーション関係が表示されます
ここからリレーション関係の設定を変更することが出来ます

image

 

Identify Relationship

Identifying Relationship (依存関係)とは、例えばビルと部屋の関係。部屋はビルなしには存在しない密な関係

Non-Identifying Relationship(非依存関係)とは、例えばレンタルDVDとユーザーの関係。ユーザーはDVDには依存していない疎の関係

 

同様に customer と customer_order の関係(1対多の非依存関係)を設定します

  1. Diagram パネル左の place a New 1:n Non-Identifying Relationship アイコン をクリック
  2. Customer_order テーブルをクリック
  3. customer テーブルをクリック

Relationship(エンティティ間の破線)をダブルクリックして Caption を is placed by に変更しておきます
Customer_order is placed by customer(注文×は 顧客yにより配置されます)

image

多対多の関係を作成

多対多の関係は、関係する双方が、関連するエンティティに対して多くの参照を持つことができます

データベース内で 多対多の関係を実装するためには、その関係を2つの 一対多 の関係に 分けて考えます
2つのテーブルの主キーが入った3番目のテーブルを設けることで関係を定義できます

顧客の注文データについて考えてみます

customer_order エンティティに次のプロパティがあります

  • 金額(amount)
  • 作成日付(date_created)
  • 確認番号(confirmation_number)
  • 注文を発行した顧客(customer_id)

しかし、注文した製品と数量のデータがありません
customer_order と product の間に 多対多 の依存関係を作成して、注文製品と数量のデータテーブルを作成します

    1. Diagram パネル左の place a New n:m Identifying Relationship アイコン をクリック
    2. Customer_order テーブルをクリック
    3. product テーブルをクリック

これだけで自動的に新しいテーブル customer_order_has_product が現れます

image

customer_order_has_product テーブルは、2つの親テーブル(customer_order と product)と依存していて、両方のテーブルからの参照を必要とします

外部キー fk_customer_order_has_product_customer_order と fk_customer_order_has_product_product が作られ、customer_order と product テーブルの主キーを参照しています

ちょっと冗長な名前なので customer_order_has_product テーブルをダブルクリックし、テーブルエディタを開いて名前を、’ordered_product’ に変更します

image

次に Indexes タブの Index name と Foreign Keys タブの foreign key name も同様に「customer_order_has_product」表記部を「ordered_product」へ修正しておきます

次に数量データのカラムを作製します
ordered_product テーブルの空欄部をダブルクリックして、quantity 列を追加します

image

こんな感じにGUI操作でデータ間の関係を見ながら設定できるので、全体像を把握しやすくなります

これで一応 ERD作成が完了です お疲れ様でした (^^)

一旦、このデータモデルを保存しておきます
Menu → Save Model から affablebean と名前を付け保存します

image

次回からは 画面下の affablebean をクリックすればデータモデルを開けます

フォワード・エンジニアリング

まだ設計図が出来ただけなので、実データベースに反映させる必要があります

作成したデータモデルをデータベースサーバーへ反映させてスキーマを作成します
この処理をフォワード・エンジニアリングと言うそうです

MySQLデータベース・サーバーが動作している必要があるので確認して下さい

メニューから Database → Forward Enginee・・・ を選択します
あとは、ウィザードに従っていけば良いだけです

image

接続するデータベース・サーバーを指定して Next をクリックします

image

DROP Objects Before Each CREATE Object と Generate DROP SCHEMA を選択して Next をクリックします

スキーマ/スキーマテーブルを作成場合に、DROP(削除)オプションが指定されていると、最初にこれらの item を削除してから再作成します(既にサーバーにある item を作成しようとすると、サーバーはエラーフラグを立てます)

image

Export MySQL Table Objects オプションがチェックされていることを確認して Next ボタンをクリックします

Show Filter ボタンをクリックすると affablebeanスキーマの中に5つのテーブルが含まれていることが確認出来ます

image

データモデルに基づいて生成された SQL スクリプトが表示されます
必要に応じて、 Save to File をクリックすればスクリプトを保存することが出来ます
Nextボタンをクリックすると実行します

image

実行結果が表示されます
うまく行ったようです。MySQLサーバ上に affablebean スキーマが作成されました (^^)

Close をクリックして、データベース・サーバーに接続してみます

image

affablebean スキーマが作成されていいるのが確認出来ます

以上がデータモデリングからフォワードエンジニアリングによるデータベースの作成手順です
ずいぶん長くなってしまいました (^^)

データベースを運用管理するためには、さらに様々なアプリケーションが必要になると思いますが、今回はその入口の設計部分に関する ERD の作成手順を紹介しました

何分にもデータベースについては初心者なので、データベース設計や運用のノウハウまでは伝えることが出来ませんが、ツールの基本的な操作方法だけはなんとか解りました

また、MySQL Workbenchには他にもデータモデリングに関連する機能があります

  • フォワード・エンジニアリング データモデルからデータベースサーバーのスキーマ作成
  • リバース・エンジニアリング データベースサーバーのスキーマからデータモデル作成
  • モデルの同期 データモデルの変更をスキーマに反映して同期

リバース・エンジニアリング等に関しては、別の機会に紹介できればと思います

では (^^)


まとめ記事紹介

go-to-top