ERツール - ER図とテーブル定義の作成

1 ERツール 概要

ERツールは、リレーショナルデータベースにおけるER図(Entity-Relationship Diagram)とテーブル作成のためのSQL文を生成するツールです。 たとえば、次のようなER図を生成できます(SVGまたはPNG形式の画像です。SVGファイル単独はこちら)。

ERD of a shop

こうしたER図を作成するために、複雑で高価なツールを使ったり、イラストソフトを使って描画したりする必要はありません。ERツールをインストールしたあと、次のようなテキストファイルを用意します。 テキストファイルの詳しい書式は、こちらを参照してください。

# オンラインショップの商品購入
都道府県
    +都道府県ID[smallint unsigned]
    都道府県名[varchar(10)]
市区町村
    +市区町村ID[int unsigned]
    都道府県ID[smallint unsigned] >都道府県
    市区町村名[varchar(20)]
顧客
    +顧客ID[serial auto_increment]
    顧客種別[varchar(10) not null]
顧客住所
    +顧客ID[bigint unsigned] >顧客
    +住所ID[serial auto_increment]
    郵便番号[varchar(20)]
    市区町村ID[int unsigned] >市区町村
    住所[varchar(100)]
    確認日時[datetime]
    無効日時[datetime]
顧客電話番号
    +顧客ID[bigint unsigned] >顧客
    +電話番号ID[serial auto_increment]
    電話番号[varchar(11)]
    確認日時[datetime]
    無効日時[datetime]
メールアドレス
    +顧客ID[bigint unsigned] >顧客{1-n}
    +メールアドレス[varchar(100)]
    確認日時[datetime]
    無効日時[datetime]
個人顧客
    +顧客ID[bigint unsigned auto_increment] >顧客{0-1}
    名字[varchar(50)]
    名前[varchar(50)]
    フリガナ名字[varchar(50)]
    フリガナ名前[varchar(50)]
法人顧客
    +顧客ID[bigint unsigned auto_increment] >顧客{0-1}
    法人名[varchar(100)]
    フリガナ法人名[varchar(50)]
    担当者[varchar(50)]
    フリガナ担当者[varchar(50)]
商品
    +商品ID[serial auto_increment]
    商品名[varchar(100)]
商品標準単価
    +商品ID[serial auto_increment] >商品{1-n}
    +開始年月日[date]
    終了年月日[date]
    標準単価[decimal(8) unsigned]
発送方法
    +発送方法ID[serial auto_increment]
    発送方法名[varchar(50)]
支払方法
    +支払方法ID[serial auto_increment]
    支払方法名[varchar(50)]
注文
    +注文番号[serial auto_increment]
    注文日時[datetime]
    消費税[decimal(6) unsigned]
    値引き[decimal(6)]
    送料[decimal(6)]
    顧客ID[bigint unsigned] >顧客
    住所ID[bigint unsigned] >顧客住所
    電話番号ID[bigint unsigned] >顧客電話番号
    発送方法ID[bigint unsigned] >発送方法
    支払方法ID[bigint unsigned] >支払方法
注文明細
    +注文番号[bigint unsigned] >注文
    +注文明細番号[serial]
    商品ID[bigint unsigned] >商品
    数量[int]
    単価[decimal(10)]

このテキストファイルをカレントディレクトリに「shop.ert」という名前で保存した場合、 次の「ert2d」コマンドを実行すれば上記のER図が出力されます。 []内の記述は、ER図を出力するときは無視されます。

$ ert2d shop.ert

ERツールはさらに、同じ.ertファイルからテーブル作成用のSQLを出力できます。 次の「ert2sql」コマンドを実行します。

$ ert2sql shop.ert

その結果、テーブル作成SQLファイルが作成されます。shop.ertでは[]内にMySQL用のデータ型などを指定しているので、 MySQL用のテーブル作成SQLファイルが作成されます。

create table 都道府県 (
    都道府県ID    smallint unsigned,
    都道府県名    varchar(10),
    primary key(都道府県ID)
);

create table 市区町村 (
    市区町村ID    int unsigned,
    都道府県ID    smallint unsigned,
    市区町村名    varchar(20),
    primary key(市区町村ID),
    foreign key(都道府県ID) references 都道府県(都道府県ID)
);

create table 顧客 (
    顧客ID    serial auto_increment,
    顧客種別    varchar(10) not null,
    primary key(顧客ID)
);

create table 顧客住所 (
    顧客ID    bigint unsigned,
    住所ID    serial auto_increment,
    郵便番号    varchar(20),
    市区町村ID    int unsigned,
    住所    varchar(100),
    確認日時    datetime,
    無効日時    datetime,
    primary key(顧客ID, 住所ID),
    foreign key(顧客ID) references 顧客(顧客ID),
    foreign key(市区町村ID) references 市区町村(市区町村ID)
);

create table 顧客電話番号 (
    顧客ID    bigint unsigned,
    電話番号ID    serial auto_increment,
    電話番号    varchar(11),
    確認日時    datetime,
    無効日時    datetime,
    primary key(顧客ID, 電話番号ID),
    foreign key(顧客ID) references 顧客(顧客ID)
);

create table メールアドレス (
    顧客ID    bigint unsigned,
    メールアドレス    varchar(100),
    確認日時    datetime,
    無効日時    datetime,
    primary key(顧客ID, メールアドレス),
    foreign key(顧客ID) references 顧客(顧客ID)
);

create table 個人顧客 (
    顧客ID    bigint unsigned auto_increment,
    名字    varchar(50),
    名前    varchar(50),
    フリガナ名字    varchar(50),
    フリガナ名前    varchar(50),
    primary key(顧客ID),
    foreign key(顧客ID) references 顧客(顧客ID)
);

create table 法人顧客 (
    顧客ID    bigint unsigned auto_increment,
    法人名    varchar(100),
    フリガナ法人名    varchar(50),
    担当者    varchar(50),
    フリガナ担当者    varchar(50),
    primary key(顧客ID),
    foreign key(顧客ID) references 顧客(顧客ID)
);

create table 商品 (
    商品ID    serial auto_increment,
    商品名    varchar(100),
    primary key(商品ID)
);

create table 商品標準単価 (
    商品ID    serial auto_increment,
    開始年月日    date,
    終了年月日    date,
    標準単価    decimal(8) unsigned,
    primary key(商品ID, 開始年月日),
    foreign key(商品ID) references 商品(商品ID)
);

create table 発送方法 (
    発送方法ID    serial auto_increment,
    発送方法名    varchar(50),
    primary key(発送方法ID)
);

create table 支払方法 (
    支払方法ID    serial auto_increment,
    支払方法名    varchar(50),
    primary key(支払方法ID)
);

create table 注文 (
    注文番号    serial auto_increment,
    注文日時    datetime,
    消費税    decimal(6) unsigned,
    値引き    decimal(6),
    送料    decimal(6),
    顧客ID    bigint unsigned,
    住所ID    bigint unsigned,
    電話番号ID    bigint unsigned,
    発送方法ID    bigint unsigned,
    支払方法ID    bigint unsigned,
    primary key(注文番号),
    foreign key(顧客ID) references 顧客(顧客ID),
    foreign key(住所ID) references 顧客住所(住所ID),
    foreign key(電話番号ID) references 顧客電話番号(電話番号ID),
    foreign key(発送方法ID) references 発送方法(発送方法ID),
    foreign key(支払方法ID) references 支払方法(支払方法ID)
);

create table 注文明細 (
    注文番号    bigint unsigned,
    注文明細番号    serial,
    商品ID    bigint unsigned,
    数量    int,
    単価    decimal(10),
    primary key(注文番号, 注文明細番号),
    foreign key(注文番号) references 注文(注文番号),
    foreign key(商品ID) references 商品(商品ID)
);

いっしょに、テーブルを削除するファイルも作成されます。

drop table 注文明細;
drop table 注文;
drop table 支払方法;
drop table 発送方法;
drop table 商品標準単価;
drop table 商品;
drop table 法人顧客;
drop table 個人顧客;
drop table メールアドレス;
drop table 顧客電話番号;
drop table 顧客住所;
drop table 顧客;
drop table 市区町村;
drop table 都道府県;

ER図とテーブル作成SQLを1つのファイルから出力できること、 すなわち「シングルソーシング」を実現できるのがERツールの大きな特徴です。

従来のツールでは、ER図を検証したあと、テーブル作成用SQLを変更するのには手間がかかります。 また、作業でミスが生じたり忘れてしまったりする危険性もあります。

ERツールを使ってER図での検証結果をERTファイルに自動で反映させれば、ER図とテーブル作成用SQLを あわせて作り直すことができます。また、ERTファイルはシンプルなテキストファイルなので、 既存のデータベースの調査結果、たとえばOracleのデータディクショナリやMySQLのinformation_schemaから 取得したデータを元にERTファイルを作ることも比較的かんたんにできます。

DataFlow of ERtool

ERツールに興味のある方は、メールにてj8takagi@nifty.comまでご連絡ください。