💽
RDBの設計における個人的なルールの整理

2024/10/02に公開
Prisma
 RDB設計の個人的な備忘録
 前提条件Prismaを主に活用
SupabaseなどのBackend as a Serviceを利用
正規化を基本としつつ、柔軟にルールを適用

 1. テーブル設計の基本方針
 1.1 命名規則テーブル名は複数形、小文字、スネークケース（例：user_profiles）
カラム名は単数形、小文字、スネークケース（例：first_name）
主キーはidで統一
外部キーは<テーブル名単数形>_id（例：user_id）

 1.2 データ型の選択文字列：VARCHAR（PrismaではString）
整数：INTEGER（PrismaではInt）
浮動小数点：DECIMAL（PrismaではDecimal）
日時：TIMESTAMP（PrismaではDateTime）
真偽値：BOOLEAN（PrismaではBoolean）
UUID：UUID（PrismaではString @db.Uuid）

 1.3 インデックス検索頻度の高いカラムにインデックスを付与
複合インデックスも考慮（検索パターンに応じて）

 1.4 主キーとシーケンスマスタデータテーブルではautoincrement を使用しない
シードデータで一意の値を設定（例：IntやString型のID）
その他のテーブルではUUIDを主キーとして使用

 1.5 Row Level Security (RLS)ユーザー関連のテーブルには全てuser_id（UUID）カラムを追加
正規化よりもセキュリティと権限管理を優先

 2. リレーションシップ
 2.1 一対多の関係子テーブルに外部キーを設置
Prismaでは@relationを使用して関係を定義

 2.2 多対多の関係中間テーブルを作成
中間テーブル名は<テーブル1>_<テーブル2>の形式

 2.3 自己参照同じテーブル内での関係（例：社員の上司）
外部キーの命名に注意（例：manager_id）

 2.4 リレーションの構造ルートとなるテーブル（例：users）に直接関連テーブルを紐づける
例：users -> profiles, users -> now_locations

数珠つなぎのリレーションは原則として避ける
例外：住所情報など、論理的に中間テーブルに紐づくべきデータ


 3. 正規化と非正規化
 3.1 基本的な正規化第三正規形まで行うことを基本とする
データの重複を避け、整合性を保つ

 3.2 非正規化の検討パフォーマンス向上が必要な場合に限定
読み取り頻度が高く、更新頻度が低いデータが対象
非正規化したデータの更新ロジックを慎重に設計
RLSのために必要な場合は正規化を無視してもよい（例：user_idの冗長な保持）

 4. NULL値の扱い可能な限りNOT NULL制約を使用
デフォルト値の設定を検討（例：空文字列、0、false）
NULL許容の場合、アプリケーション側でのハンドリングに注意

 5. バージョン管理とマイグレーションPrismaのマイグレーション機能を活用
各マイグレーションファイルにコメントを付与
本番環境へのマイグレーション適用は慎重に行う
Database functions、RLS、extensionsなどの設定もSQLファイルで管理

 6. セキュリティ考慮事項パスワードなどの機密情報はハッシュ化して保存
個人情報を含むカラムは暗号化を検討
RLS（Row Level Security）の活用

user_idカラムを利用して適切なポリシーを設定
テーブルごとにRLSポリシーを明確に定義


 7. パフォーマンスチューニング適切なインデックス設計
クエリの最適化（Prismaのクエリ最適化機能を活用）
必要に応じてビューやマテリアライズドビューの使用

 8. ドキュメンテーションERダイアグラムの作成と更新
各テーブル、カラムの説明ドキュメントの維持
設計判断の理由や背景の記録

 9. シードデータとマスタデータの管理カテゴリーなどのマスタデータはシードを利用して初期化
シードデータ内で一意のIDを設定（autoincrement を使用しない）
シードデータの更新手順とバージョン管理を明確に

 10. データベース設定のコード管理（IaC）
 10.1 SQLファイルによる管理Database functions、RLS、extensionsなどの設定をSQLファイルで管理
GUIツールの使用を最小限に抑え、設定の再現性を確保

 10.2 バージョン管理SQLファイルをGitなどのバージョン管理システムで管理
変更履歴を追跡し、設定の変更を明確に

 10.3 自動化とCI/CDSQLファイルの適用を自動化スクリプトやCI/CDパイプラインに組み込む
環境間（開発、ステージング、本番）での一貫性を確保

 10.4 ドキュメンテーション各SQLファイルの目的と影響範囲をコメントで明記
README.mdファイルで全体の構成と適用手順を説明

 10.5 テスト設定変更の影響を検証するためのテストスクリプトを用意
CI/CDパイプラインでテストを自動実行

 10.6 ロールバック戦略各設定変更に対するロールバック用SQLファイルを用意
問題発生時の迅速な対応を可能に

 11. Prismaスキーマ定義サンプル以下に、これまでの設計方針を反映し、マスタデータのIDを意味のある文字列に変更したPrismaスキーマ定義のサンプルを示します。
// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

// ユーザーテーブル
model User {
  id            String    @id @default(uuid()) @db.Uuid
  email         String    @unique
  password_hash String
  created_at    DateTime  @default(now())
  updated_at    DateTime  @updatedAt

  profile       Profile?
  now_location  NowLocation?
  posts         Post[]

  @@map("users")
}

// プロフィールテーブル
model Profile {
  id         String   @id @default(uuid()) @db.Uuid
  user_id    String   @unique @db.Uuid
  first_name String
  last_name  String
  bio        String?
  created_at DateTime @default(now())
  updated_at DateTime @updatedAt

  user       User     @relation(fields: [user_id], references: [id])
  address    Address?

  @@map("profiles")
}

// 現在地テーブル
model NowLocation {
  id         String   @id @default(uuid()) @db.Uuid
  user_id    String   @unique @db.Uuid
  latitude   Float
  longitude  Float
  created_at DateTime @default(now())
  updated_at DateTime @updatedAt

  user       User     @relation(fields: [user_id], references: [id])

  @@map("now_locations")
}

// 住所テーブル
model Address {
  id         String   @id @default(uuid()) @db.Uuid
  profile_id String   @unique @db.Uuid
  street     String
  city       String
  state      String
  country    String
  zip_code   String
  created_at DateTime @default(now())
  updated_at DateTime @updatedAt

  profile    Profile  @relation(fields: [profile_id], references: [id])

  @@map("addresses")
}

// 投稿テーブル
model Post {
  id         String   @id @default(uuid()) @db.Uuid
  user_id    String   @db.Uuid
  title      String
  content    String
  published  Boolean  @default(false)
  created_at DateTime @default(now())
  updated_at DateTime @updatedAt

  user       User     @relation(fields: [user_id], references: [id])
  categories PostCategory[]

  @@map("posts")
}

// カテゴリーテーブル（マスタデータ）
model Category {
  id          String   @id
  name        String   @unique
  description String?
  created_at  DateTime @default(now())
  updated_at  DateTime @updatedAt

  posts       PostCategory[]

  @@map("categories")
}

// 投稿とカテゴリーの中間テーブル
model PostCategory {
  post_id     String   @db.Uuid
  category_id String
  created_at  DateTime @default(now())

  post        Post     @relation(fields: [post_id], references: [id])
  category    Category @relation(fields: [category_id], references: [id])

  @@id([post_id, category_id])
  @@map("post_categories")
}
このサンプルスキーマでの主な変更点：
CategoryモデルのidフィールドをString型に変更しました。これにより、意味のある文字列ID（例：'tech', 'lifestyle', 'news'など）を使用できます。
PostCategoryモデルのcategory_idも対応してString型に変更しています。
これらの変更により、以下のような利点があります：
フロントエンドでのカテゴリー管理が容易になります。IDそのものが意味を持つため、別途日本語名との対応付けが不要になる場合があります。
データベース上でも、カテゴリーの意味が直感的に理解しやすくなります。
将来的なカテゴリーの追加や変更が柔軟に行えます。
ただし、以下の点に注意が必要です：
IDの一意性を保つため、カテゴリーIDの命名規則を明確に定義し、一貫性を保つことが重要です。
IDに使用する文字列は、URLやファイル名として使用しても問題ない文字のみを使用するべきです（英数字、ハイフン、アンダースコアなど）。
将来的なカテゴリーの国際化対応を考慮し、IDは英語ベースにすることを推奨します。

 シードデータの例カテゴリーテーブルのシードデータは以下のようになります：
const categories = [
  { id: 'tech', name: '技術', description: '技術関連の記事' },
  { id: 'lifestyle', name: 'ライフスタイル', description: '日常生活に関する記事' },
  { id: 'news', name: 'ニュース', description: '最新のニュース記事' },
  { id: 'health', name: '健康', description: '健康・医療に関する記事' },
  { id: 'culture', name: '文化', description: '文化・芸術に関する記事' },
];

await prisma.category.createMany({
  data: categories,
  skipDuplicates: true,
});
この方法により、IDは英語ベースで管理しつつ、nameフィールドで日本語表示用の名称を持つことができます。フロントエンドではIDを使用してルーティングや処理を行い、表示にはnameを使用するという使い分けが可能になります。

 まとめ以上、PrismaとSupabaseを活用したRDB設計についての私見をまとめてみました。データベース設計は、プロジェクトの成功を左右する重要な要素です。正規化を基本としつつも、パフォーマンスやセキュリティの要件に応じて柔軟に対応することが大切だと考えています。
Prismaを使用することで、TypeScriptとの親和性が高く、型安全なデータベース操作が可能になります。これは、長期的なプロジェクトの保守性向上に大きく貢献するでしょう。
忘れがちなのは、マスタデータテーブルでの意味のある文字列IDの使用です。これにより、データベースの可読性が格段に向上し、開発者間のコミュニケーションもスムーズになります。また、Row Level Security (RLS) の活用やSQLファイルによるデータベース設定の管理など、セキュリティと保守性を高める工夫も重要ですね。
ここで紹介した方法は、あくまで一つの指針です。プロジェクトの規模や要件によって、最適な設計は変わってくるかもしれません。大切なのは、チーム内で設計方針を共有し、一貫性を保つことです。
みなさんも、自身のプロジェクトでデータベース設計を行う際は、ここで紹介した内容を参考にしつつ、プロジェクトに最適な設計を模索してみてください。そして、得られた知見や工夫があれば、ぜひコメント欄で共有してください。他の開発者の経験から学ぶことも、技術力向上の大切な要素だと思います。
データベース設計は奥が深く、常に学びがあります。これからも新しい技術や手法をキャッチアップしながら、より良い設計を追求していきたいと思います。一緒にデータベース設計の世界を探求していきましょう！
RDB設計の個人的な備忘録

前提条件

1. テーブル設計の基本方針

1.1 命名規則

1.2 データ型の選択

1.3 インデックス

1.4 主キーとシーケンス

1.5 Row Level Security (RLS)

2. リレーションシップ

2.1 一対多の関係

2.2 多対多の関係

2.3 自己参照

2.4 リレーションの構造

3. 正規化と非正規化

3.1 基本的な正規化

3.2 非正規化の検討

4. NULL値の扱い

5. バージョン管理とマイグレーション

6. セキュリティ考慮事項

7. パフォーマンスチューニング

8. ドキュメンテーション

9. シードデータとマスタデータの管理

10. データベース設定のコード管理（IaC）

10.1 SQLファイルによる管理

10.2 バージョン管理

10.3 自動化とCI/CD

10.4 ドキュメンテーション

10.5 テスト

10.6 ロールバック戦略

11. Prismaスキーマ定義サンプル

シードデータの例

まとめ

Discussion
