Posted inデータベース

dbdiagram.ioでERD設計：概念からSQL自動生成まで

Database tutorial - IT technology blog
Database tutorial - IT technology blog

「場当たり的なスキーマ設計」の問題点

新しいプロジェクトを始めたとき、SQLクライアントを開いてusersテーブルを作り、いくつかカラムを追加する。するとすぐにordersテーブルが必要になり、次にproductsテーブル、さらに中間テーブルと続く——そのうち外部キーの向きがおかしくなり、テーブル間でカラム名が統一されていないことに気づく。

これが私の言う「場当たり的なスキーマ設計」です。ほとんどの開発者が経験することでしょう。解決策はSQLをより注意深く書くことではありません。コードを1行も書く前に、20分かけてスキーマを視覚的に描くことです。

それがdbdiagram.ioの目的です。ブラウザベースのERDツールで、DBMLというシンプルなマークアップ言語でデータベーススキーマを描き、ワンクリックでPostgreSQL、MySQL、またはSQL Server向けのSQLを自動生成できます。

基本概念：ERD、エンティティ、リレーションシップ

ER図（ERD）はデータベースの視覚的なマップです。テーブル（エンティティ）、そのカラム（属性）、テーブル間のつながり（リレーションシップ）を示します。建築家の設計図のようなもの——設計図なしに家は建てられませんよね。

エンティティと属性

エンティティとは基本的にテーブルのことです。各エンティティは属性を持ちます——名前とデータ型を持つカラムです。Userエンティティであれば、id、email、created_atといった属性を持つでしょう。SQLを書く前にこれらを描き出すことで、実際に必要なデータと単なる推測で追加しようとしているデータを区別して考えることができます。

リレーションシップとカーディナリティ

リレーションシップはエンティティ同士のつながりを示します。主な種類は以下の通りです：

  • 1対1（1:1） — 1人のユーザーは1つのプロフィールを持つ。
  • 1対多（1:N） — 1人のユーザーは複数の注文を行える。
  • 多対多（M:N） — 複数の注文が複数の商品を含むことができ、中間テーブルで管理する。

設計段階でカーディナリティを正しく定義することで、後のリファクタリングに費やす時間を大幅に削減できます。視覚的に描くことで、本番のマイグレーションに組み込まれる前にミスを見つけやすくなります。

DBML構文

DBML（Database Markup Language）はdbdiagram.ioで使用するDSLです。クリーンで読みやすく、生のSQLを書くよりもドキュメントを書く感覚に近いです。最小限の例を見てみましょう：

Table users {
  id integer [primary key, increment]
  email varchar [not null, unique]
  password_hash varchar [not null]
  created_at timestamp [default: `now()`]
}

各Tableブロックはテーブルを定義します。カラム定義はcolumn_name data_type [constraints]というパターンに従います。リレーションシップはテーブルブロックの外にRef:文を使って記述します：

Ref: orders.user_id > users.id  // 多対1

矢印の向きが重要です：>は多対1、<は1対多、-は1対1を意味します。

実践：ECサイトのスキーマ設計

dbdiagram.ioにアクセスして、Create your diagramをクリックします。開始にアカウントは不要です。インターフェイスは2つのパネルに分かれています：左側がコードエディタ、右側がリアルタイムのビジュアル図です。入力するたびに図が即座に更新されます。

シンプルなECプラットフォームのスキーマを設計します。ユーザー、商品、注文、そして1つの注文に複数の商品を紐付けるための仕組みが必要です。

ステップ1：まずエンティティを定義する

DBMLを書く前に、何を保存する必要があるかをリストアップします——どのように保存するかではなく。このECシステムの場合：

  • Users — 購入者
  • Products — 販売商品
  • Orders — ユーザーに紐づく購入イベント
  • Order Items — 注文と商品を結ぶ中間テーブル

4つのエンティティです。次に、各エンティティに必要な主要属性をメモします。この5分の作業だけで、スキーマのミスのほとんどを防ぐことができます。

ステップ2：DBMLを書く

以下をdbdiagram.ioのエディタに貼り付けてください：

Table users {
  id integer [primary key, increment]
  email varchar [not null, unique]
  full_name varchar
  created_at timestamp [default: `now()`]
}

Table products {
  id integer [primary key, increment]
  name varchar [not null]
  description text
  price decimal(10,2) [not null]
  stock_quantity integer [default: 0]
  created_at timestamp [default: `now()`]
}

Table orders {
  id integer [primary key, increment]
  user_id integer [not null]
  status varchar [default: 'pending']  // pending（保留中）、paid（支払済）、shipped（発送済）、delivered（配達済）
  total_amount decimal(10,2)
  created_at timestamp [default: `now()`]
}

Table order_items {
  id integer [primary key, increment]
  order_id integer [not null]
  product_id integer [not null]
  quantity integer [not null]
  unit_price decimal(10,2) [not null]  // 購入時の価格
}

入力するたびに、図は各テーブルをカラム一覧付きのボックスとして描画します。生のSQLファイルでは見落としがちな命名の不統一や欠落カラムをすぐに発見できます。

ステップ3：リレーションシップを追加する

テーブルをつなぎましょう。テーブル定義の下に以下のRef:文を追加してください：

Ref: orders.user_id > users.id           // 複数の注文が1人のユーザーに属する
Ref: order_items.order_id > orders.id    // 複数のアイテムが1つの注文に属する
Ref: order_items.product_id > products.id // 複数のアイテムが1つの商品を参照する

図はすぐに接続線を描画します。order_itemsがordersとproductsの間に位置し、多対多のパターンを処理していることが視覚的にわかります。

unit_priceをorder_itemsに保存しており、products.priceを参照するだけにしていない点に注目してください。これは意図的な設計です——商品価格は時間とともに変わりますが、過去の注文には顧客が実際に支払った金額を反映させなければなりません。このような設計上の判断は、6ヶ月後にマイグレーションファイルの奥深くに埋もれてしまう前に、ERDで発見する方がはるかに簡単です。

ステップ4：SQLにエクスポートする

図が正しく見えたら、右上のExportボタンをクリックします。ターゲットデータベース（PostgreSQL、MySQL、またはMSSQL）を選択します。dbdiagram.ioは外部キー制約を含む完全なCREATE TABLE文を生成します：

CREATE TABLE "users" (
  "id" integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  "email" varchar UNIQUE NOT NULL,
  "full_name" varchar,
  "created_at" timestamp DEFAULT (now())
);

CREATE TABLE "order_items" (
  "id" integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  "order_id" integer NOT NULL,
  "product_id" integer NOT NULL,
  "quantity" integer NOT NULL,
  "unit_price" decimal(10,2) NOT NULL
);

ALTER TABLE "orders" ADD FOREIGN KEY ("user_id") REFERENCES "users" ("id");
ALTER TABLE "order_items" ADD FOREIGN KEY ("order_id") REFERENCES "orders" ("id");
ALTER TABLE "order_items" ADD FOREIGN KEY ("product_id") REFERENCES "products" ("id");

それをデータベースクライアントまたはマイグレーションツールにコピーすれば完了です。ボイラープレートの記述も、制約構文のタイプミスも不要です。

ボーナス：既存スキーマのインポート

すでに本番データベースがあって視覚的にドキュメント化したい場合は、まずスキーマをダンプします：

# PostgreSQL — スキーマのみ、データなし
pg_dump --schema-only -U your_user -d your_database > schema.sql

# MySQL
mysqldump --no-data -u your_user -p your_database > schema.sql

次にdbdiagram.ioで：Importをクリック→Import from PostgreSQL（またはMySQL）→SQLを貼り付けます。DBMLをリバースエンジニアリングして図を自動的に描画します。レガシーコードベースへのオンボーディングに便利です。

よりクリーンなスキーマ設計のためのヒント

複数のプロジェクトでこのワークフローを実践してきた中で気づいたことをいくつか紹介します：

  • カラム名を統一する。 あるテーブルでcreated_atを使うなら、すべてのテーブルで統一する——テーブルによってcreated、date_created、creation_dateと異なる名前を使わない。
  • 価格と数量は必ずスナップショットで保存する。 過去の注文データにproductsテーブルからのリアルタイム参照を使わない。取引時のunit_priceとquantityを保存する。
  • 外部キーにインデックスを追加する。 DBMLはテーブル定義内にindexesブロックをサポートしています。インデックスのない外部キーはJOIN時にフルテーブルスキャンを引き起こし、データが増えると深刻な問題になります。
  • 大規模スキーマにはTableGroupを使う。 テーブルが20以上ある場合、TableGroupブロックで関連するテーブルをグループ化して図を見やすく保つ。
Table order_items {
  id integer [primary key, increment]
  order_id integer [not null]
  product_id integer [not null]
  quantity integer [not null]
  unit_price decimal(10,2) [not null]

  indexes {
    order_id
    product_id
  }
}

TableGroup ecommerce {
  users
  products
  orders
  order_items
}

もう一つのヒント：スプレッドシートからの初期データでデータベースにシードする場合、インポート前にCSVエクスポートを変換する必要があることがよくあります。素早いCSV→JSON変換にはtoolcraft.app/ja/tools/data/csv-to-jsonを使っています——ブラウザ内で完全に動作するためデータが自分のマシンから外に出ず、シードデータに顧客情報や価格情報などの機密データが含まれる場合に重要です。

まとめ

最初のマイグレーションの前にdbdiagram.ioで20分使うだけで、後のリファクタリングに費やす時間を何時間も節約できます。DBMLの構文はすぐに習得でき、ライブ図はリレーションシップのエラーを即座に検出し、SQLエクスポートによってCREATE TABLEのボイラープレートをすべてスキップできます。

身につけるべき習慣はこれです：データベースに触れる機能の開発を始めるときは、まずテーブルを描く。3〜4のテーブルからなる大まかなERDでも、ロールバックが困難なマイグレーションにコミットする前に、リレーションシップ、命名、データ型を整理することができます。

このガイドのECサイトの例を参考に、自分のドメインに置き換えてみてください——ブログCMS、在庫管理システム、SaaSサブスクリプションモデルなど。ERDファーストのパターンは変わらず、将来の自分が自分自身のスキーマをリバースエンジニアリングしなくて済むたびに、今の自分に感謝することでしょう。

Share:
Tags: