database-design

データベース設計

要件からデータベーススキーマを設計します。

ワークフロー

1. エンティティを特定する

要件から主要なエンティティ（名詞）を抽出します。

• ユーザー、チーム、プロジェクト、タスク、コメントなど
• 各エンティティをテーブルにします

2. リレーションシップを定義する

リレーションシップ	実装
一対一	一意制約付きの外部キー、または同じテーブルに埋め込み
一対多	「多」側に外部キーを設定
多対多	中間テーブル／結合テーブル
自己参照	同じテーブルを指す外部キー（例：parent_id）

3. スキーマを設計する

各テーブルについて：

CREATE TABLE users (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email       TEXT NOT NULL UNIQUE,
  name        TEXT NOT NULL,
  avatar_url  TEXT,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE projects (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name        TEXT NOT NULL,
  owner_id    UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

4. ベストプラクティスを適用する

主キー：

• 分散システムや一般公開する ID には UUID を使用します
• 内部でのみ使用する ID には SERIAL／BIGSERIAL を使用します（結合が高速です）

タイムスタンプ：

• 常に created_at と updated_at を追加します
• TIMESTAMPTZ（タイムゾーン付き）を使用し、TIMESTAMP は使用しません

命名：

• テーブル：複数形のスネークケース（users、project_members）
• 列：単数形のスネークケース（user_id、created_at）
• インデックス：idx_<table>_<columns>（idx_users_email）

制約：

• 本当に省略可能な場合を除き、すべてに NOT NULL を設定します
• 自然キー（メールアドレス、スラッグ、外部 ID）には UNIQUE を設定します
• ON DELETE の動作（CASCADE、SET NULL、RESTRICT）を指定した REFERENCES を設定します
• 列挙値や値の範囲には CHECK 制約を設定します

5. インデックスを追加する

-- For columns you filter/sort by frequently
CREATE INDEX idx_projects_owner_id ON projects(owner_id);

-- For unique lookups
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Composite for common query patterns
CREATE INDEX idx_tasks_project_status ON tasks(project_id, status);

インデックスを設定する箇所：

• 外部キー（ほとんどの場合）
• WHERE 句で使う列
• ORDER BY で使う列
• JOIN 条件で使う列

インデックスを設定しない箇所：

• 小規模なテーブル（1,000 行未満）
• カーディナリティの低い列（ブール値、3 種類の値を持つステータス）
• クエリでほとんど使わない列

6. ORM のセットアップ

Prisma：

model User {
  id        String   @id @default(uuid())
  email     String   @unique
  name      String
  projects  Project[]
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")
  @@map("users")
}

Drizzle：

export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  email: text('email').notNull().unique(),
  name: text('name').notNull(),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
});

よく使われるパターン

論理削除： 行を実際に削除する代わりに、deleted_at TIMESTAMPTZ を追加します
監査ログ： entity_type、entity_id、action、actor_id、payload を持つ独立した audit_events テーブルを作成します
タグ／ラベル： task_id と tag_id を持つ中間テーブル（task_tags）を作成します
ツリー／階層： parent_id で自己参照するか、マテリアライズドパス（/1/4/7/）を使います
ポリモーフィックな関連付け： entity_type と entity_id の列を使います（可能であれば避け、個別の外部キーを使います）

ヒント

• まずは正規化（第 3 正規形）し、パフォーマンスの問題を測定してから非正規化します
• キャッシュ／無効化の戦略がない限り、導出データを保存しないでください
• ステータスフィールドには自由入力のテキストではなく、データベースの列挙型または CHECK 制約を使います
• 親レコードを削除したときにどうなるかを、必ず検討してください