ユーザー認証システムのERD

認証システムスキーマの設計

堅牢なユーザー認証システムにはいくつかの関連テーブルが必要です。このERDはユーザー登録、ログイン、ロールベースアクセス制御（RBAC）、セッション管理に必要なコアエンティティをカバーします。

コアエンティティ

1. users — 中心的なエンティティ

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(320) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  is_active BOOLEAN NOT NULL DEFAULT true,
  is_email_verified BOOLEAN NOT NULL DEFAULT false,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

2. roles — 名前付き権限グループ

CREATE TABLE roles (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE,
  description TEXT
);

3. user_roles — 結合テーブル（usersとrolesのM:N）

CREATE TABLE user_roles (
  user_id INT NOT NULL,
  role_id INT NOT NULL,
  assigned_at TIMESTAMP NOT NULL DEFAULT NOW(),
  PRIMARY KEY (user_id, role_id),
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (role_id) REFERENCES roles(id)
);

4. permissions — 細粒度のアクセス権

CREATE TABLE permissions (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE,
  resource VARCHAR(100) NOT NULL,
  action VARCHAR(50) NOT NULL
);

5. role_permissions — 結合テーブル（rolesとpermissionsのM:N）

CREATE TABLE role_permissions (
  role_id INT NOT NULL,
  permission_id INT NOT NULL,
  PRIMARY KEY (role_id, permission_id),
  FOREIGN KEY (role_id) REFERENCES roles(id),
  FOREIGN KEY (permission_id) REFERENCES permissions(id)
);

6. sessions — アクティブなユーザーセッション（usersから1:N）

CREATE TABLE sessions (
  id UUID PRIMARY KEY,
  user_id INT NOT NULL,
  ip_address VARCHAR(45),
  user_agent TEXT,
  expires_at TIMESTAMP NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

リレーションシップの概要

From	To	タイプ
users	user_roles	1:N
roles	user_roles	1:N
roles	role_permissions	1:N
permissions	role_permissions	1:N
users	sessions	1:N

これにより、ユーザーにロールが割り当てられ、ロールにパーミッションが含まれるRBACシステムが作成されます。ユーザーが特定のパーミッションを持っているかどうかは、users → user_roles → roles → role_permissions → permissionsをたどって確認できます。