Skip to main content

PLANAV API 詳細設計提案とデータベーススキーマ

[前半部分は省略...]

11. SQLデータベーススキーマ​

以下に、主要なテーブルのSQLスキーマを示します。

Users テーブル​

CREATE TABLE Users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    firebase_id VARCHAR(255) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(50) UNIQUE NOT NULL,
    stage ENUM('alpha', 'beta', 'stable') NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Goals テーブル​

CREATE TABLE Goals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    category VARCHAR(50),
    icon VARCHAR(50),
    color VARCHAR(7),
    deadline DATE,
    extend BOOLEAN DEFAULT FALSE,
    unit VARCHAR(20),
    total DECIMAL(10, 2),
    allocation DECIMAL(10, 2),
    priority INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    archived_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES Users(id) ON DELETE CASCADE
);

DailyTasks テーブル​

CREATE TABLE DailyTasks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    goal_id INT NOT NULL,
    date DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    note TEXT,
    status ENUM('not_started', 'in_progress', 'completed') DEFAULT 'not_started',
    difficulty ENUM('easy', 'medium', 'hard'),
    estimated_time INT, -- in minutes
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (goal_id) REFERENCES Goals(id) ON DELETE CASCADE
);

GoalTags テーブル​

CREATE TABLE GoalTags (
    id INT AUTO_INCREMENT PRIMARY KEY,
    goal_id INT NOT NULL,
    tag VARCHAR(50) NOT NULL,
    FOREIGN KEY (goal_id) REFERENCES Goals(id) ON DELETE CASCADE
);

GoalDependencies テーブル​

CREATE TABLE GoalDependencies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    goal_id INT NOT NULL,
    dependent_goal_id INT NOT NULL,
    FOREIGN KEY (goal_id) REFERENCES Goals(id) ON DELETE CASCADE,
    FOREIGN KEY (dependent_goal_id) REFERENCES Goals(id) ON DELETE CASCADE
);

Notifications テーブル​

CREATE TABLE Notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    type ENUM('deadline', 'reminder', 'achievement') NOT NULL,
    content TEXT NOT NULL,
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES Users(id) ON DELETE CASCADE
);

UserSettings テーブル​

CREATE TABLE UserSettings (
    user_id INT PRIMARY KEY,
    notification_preferences JSON,
    theme VARCHAR(20) DEFAULT 'light',
    language VARCHAR(10) DEFAULT 'en',
    FOREIGN KEY (user_id) REFERENCES Users(id) ON DELETE CASCADE
);

ProgressReports テーブル​

CREATE TABLE ProgressReports (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    report_date DATE NOT NULL,
    overall_progress DECIMAL(5, 2),
    category_progress JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES Users(id) ON DELETE CASCADE
);

Backups テーブル​

CREATE TABLE Backups (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    backup_data LONGBLOB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES Users(id) ON DELETE CASCADE
);

このスキーマは、前述の機能要件と詳細設計提案に基づいています。主な特徴は以下の通りです：

  1. Usersテーブル: 基本的なユーザー情報を格納します。
  2. Goalsテーブル: 目標の詳細情報を格納し、ユーザーとの関連付けを行います。
  3. DailyTasksテーブル: 日々のタスクを管理し、目標との関連付けを行います。
  4. GoalTagsとGoalDependenciesテーブル: 目標のタグ付けと依存関係を管理します。
  5. Notificationsテーブル: ユーザーへの通知を管理します。
  6. UserSettingsテーブル: ユーザー固有の設定を保存します。
  7. ProgressReportsテーブル: 進捗状況のレポートを保存します。
  8. Backupsテーブル: ユーザーデータのバックアップを管理します。

注意点：

  • 適切なインデックスを追加して、クエリのパフォーマンスを最適化してください。
  • セキュリティ上の理由から、パスワードは保存していません（Firebase認証を使用することを想定）。
  • JSON型を使用しているカラムは、柔軟性を持たせるためです。必要に応じて正規化することも検討してください。

このスキーマは基本的な構造を提供していますが、実際の実装時には、アプリケーションの具体的な要件やパフォーマンスの考慮事項に基づいて調整が必要になる可能性があります。