822 lines
26 KiB
Markdown
822 lines
26 KiB
Markdown
# 資料庫架構設計
|
||
|
||
## 概述
|
||
基於 `system_structure_design.json` 中定義的9個數據源設計完整的資料庫架構，支援Drama Ling應用的所有功能需求。
|
||
|
||
## 資料庫設計原則
|
||
|
||
### 設計原則
|
||
- [ ] **正規化設計**: 遵循第三正規化原則，減少資料重複
|
||
- [ ] **效能優化**: 針對查詢頻繁的欄位建立適當索引
|
||
- [ ] **擴展性**: 設計支援未來功能擴展的彈性架構
|
||
- [ ] **資料完整性**: 使用外鍵約束確保資料一致性
|
||
- [ ] **安全性**: 敏感資料加密存儲，存取權限控制
|
||
|
||
### 技術選型
|
||
- [ ] **主資料庫**: PostgreSQL 15+ (關聯式資料庫)
|
||
- [ ] **快取層**: Redis 7+ (高速快取和Session存儲)
|
||
- [ ] **搜尋引擎**: Elasticsearch 8+ (全文檢索和分析)
|
||
- [ ] **檔案存儲**: AWS S3 / MinIO (多媒體檔案存儲)
|
||
- [ ] **時序資料**: InfluxDB (學習行為分析資料)
|
||
|
||
## 核心資料表設計
|
||
|
||
### 1. 用戶資料表 (UserProfile)
|
||
|
||
```sql
|
||
CREATE TABLE users (
|
||
user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
username VARCHAR(50) UNIQUE NOT NULL,
|
||
email VARCHAR(255) UNIQUE NOT NULL,
|
||
password_hash VARCHAR(255) NOT NULL,
|
||
salt VARCHAR(50) NOT NULL,
|
||
|
||
-- 基本資料
|
||
avatar_url TEXT,
|
||
first_name VARCHAR(100),
|
||
last_name VARCHAR(100),
|
||
date_of_birth DATE,
|
||
gender VARCHAR(20),
|
||
country_code CHAR(2),
|
||
timezone VARCHAR(50) DEFAULT 'UTC',
|
||
|
||
-- 語言設定
|
||
native_language VARCHAR(10) NOT NULL DEFAULT 'zh-TW',
|
||
target_languages JSONB DEFAULT '["en"]',
|
||
current_level VARCHAR(10) DEFAULT 'A1',
|
||
|
||
-- 學習統計
|
||
total_score INTEGER DEFAULT 0,
|
||
total_experience INTEGER DEFAULT 0,
|
||
current_streak INTEGER DEFAULT 0,
|
||
longest_streak INTEGER DEFAULT 0,
|
||
total_study_time INTEGER DEFAULT 0, -- minutes
|
||
|
||
-- 偏好設定
|
||
preferences JSONB DEFAULT '{}',
|
||
notification_settings JSONB DEFAULT '{}',
|
||
|
||
-- 系統欄位
|
||
status VARCHAR(20) DEFAULT 'active',
|
||
email_verified BOOLEAN DEFAULT FALSE,
|
||
last_login_at TIMESTAMPTZ,
|
||
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
||
|
||
-- 索引
|
||
CONSTRAINT users_username_check CHECK (length(username) >= 3),
|
||
CONSTRAINT users_email_check CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
|
||
);
|
||
|
||
-- 索引
|
||
CREATE INDEX idx_users_username ON users(username);
|
||
CREATE INDEX idx_users_email ON users(email);
|
||
CREATE INDEX idx_users_status ON users(status);
|
||
CREATE INDEX idx_users_country ON users(country_code);
|
||
CREATE INDEX idx_users_created_at ON users(created_at);
|
||
```
|
||
|
||
### 2. 課程場景表 (Lesson)
|
||
|
||
```sql
|
||
CREATE TABLE lessons (
|
||
lesson_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
lesson_code VARCHAR(50) UNIQUE NOT NULL, -- SC_Restaurant_01
|
||
|
||
-- 基本資訊
|
||
title VARCHAR(200) NOT NULL,
|
||
description TEXT,
|
||
long_description TEXT,
|
||
category VARCHAR(50) NOT NULL, -- daily_life, social, emergency, professional
|
||
sub_category VARCHAR(50),
|
||
|
||
-- 難度與分級
|
||
difficulty_level VARCHAR(10) NOT NULL, -- A1, A2, B1, B2, C1, C2
|
||
estimated_duration INTEGER, -- minutes
|
||
complexity_score INTEGER DEFAULT 1, -- 1-10
|
||
|
||
-- 學習目標
|
||
learning_objectives JSONB DEFAULT '[]',
|
||
target_vocabulary JSONB DEFAULT '[]',
|
||
grammar_points JSONB DEFAULT '[]',
|
||
|
||
-- 內容資料
|
||
scenario_data JSONB NOT NULL, -- 對話流程、角色設定等
|
||
cultural_notes TEXT,
|
||
background_audio_url TEXT,
|
||
thumbnail_url TEXT,
|
||
|
||
-- 解鎖條件
|
||
prerequisite_lessons JSONB DEFAULT '[]',
|
||
unlock_conditions JSONB DEFAULT '{}',
|
||
is_premium BOOLEAN DEFAULT FALSE,
|
||
|
||
-- 統計資料
|
||
popularity_score DECIMAL(3,2) DEFAULT 0.00,
|
||
average_rating DECIMAL(3,2) DEFAULT 0.00,
|
||
completion_rate DECIMAL(3,2) DEFAULT 0.00,
|
||
|
||
-- 系統欄位
|
||
status VARCHAR(20) DEFAULT 'active',
|
||
published_at TIMESTAMPTZ,
|
||
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
||
|
||
CONSTRAINT lessons_difficulty_check CHECK (difficulty_level IN ('A1', 'A2', 'B1', 'B2', 'C1', 'C2')),
|
||
CONSTRAINT lessons_category_check CHECK (category IN ('daily_life', 'social', 'emergency', 'professional'))
|
||
);
|
||
|
||
-- 索引
|
||
CREATE INDEX idx_lessons_category ON lessons(category);
|
||
CREATE INDEX idx_lessons_difficulty ON lessons(difficulty_level);
|
||
CREATE INDEX idx_lessons_status ON lessons(status) WHERE status = 'active';
|
||
CREATE INDEX idx_lessons_premium ON lessons(is_premium);
|
||
CREATE INDEX idx_lessons_popularity ON lessons(popularity_score DESC);
|
||
```
|
||
|
||
### 3. 對話記錄表 (Dialogue)
|
||
|
||
```sql
|
||
CREATE TABLE dialogues (
|
||
dialogue_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id UUID REFERENCES users(user_id) ON DELETE CASCADE,
|
||
lesson_id UUID REFERENCES lessons(lesson_id) ON DELETE SET NULL,
|
||
|
||
-- 對話基本資訊
|
||
session_token VARCHAR(255) UNIQUE,
|
||
scenario_context JSONB, -- 場景設定、角色資訊
|
||
|
||
-- 對話進度
|
||
status VARCHAR(20) DEFAULT 'in_progress', -- in_progress, completed, abandoned
|
||
turn_count INTEGER DEFAULT 0,
|
||
current_objective VARCHAR(100),
|
||
objectives_completed JSONB DEFAULT '[]',
|
||
|
||
-- 評分與分析
|
||
grammar_score INTEGER,
|
||
semantic_score INTEGER,
|
||
fluency_score INTEGER,
|
||
overall_score INTEGER,
|
||
ai_feedback JSONB,
|
||
|
||
-- 時間記錄
|
||
started_at TIMESTAMPTZ DEFAULT NOW(),
|
||
completed_at TIMESTAMPTZ,
|
||
total_duration INTEGER, -- seconds
|
||
|
||
-- 詞彙使用
|
||
vocabulary_used JSONB DEFAULT '[]',
|
||
new_vocabulary_encountered JSONB DEFAULT '[]',
|
||
|
||
-- 系統欄位
|
||
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
||
|
||
CONSTRAINT dialogues_status_check CHECK (status IN ('in_progress', 'completed', 'abandoned')),
|
||
CONSTRAINT dialogues_scores_check CHECK (
|
||
(grammar_score IS NULL OR grammar_score BETWEEN 0 AND 100) AND
|
||
(semantic_score IS NULL OR semantic_score BETWEEN 0 AND 100) AND
|
||
(fluency_score IS NULL OR fluency_score BETWEEN 0 AND 100) AND
|
||
(overall_score IS NULL OR overall_score BETWEEN 0 AND 100)
|
||
)
|
||
);
|
||
|
||
-- 索引
|
||
CREATE INDEX idx_dialogues_user ON dialogues(user_id);
|
||
CREATE INDEX idx_dialogues_lesson ON dialogues(lesson_id);
|
||
CREATE INDEX idx_dialogues_status ON dialogues(status);
|
||
CREATE INDEX idx_dialogues_completed ON dialogues(completed_at) WHERE completed_at IS NOT NULL;
|
||
CREATE INDEX idx_dialogues_score ON dialogues(overall_score) WHERE overall_score IS NOT NULL;
|
||
```
|
||
|
||
### 4. 對話訊息表 (DialogueMessages)
|
||
|
||
```sql
|
||
CREATE TABLE dialogue_messages (
|
||
message_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
dialogue_id UUID REFERENCES dialogues(dialogue_id) ON DELETE CASCADE,
|
||
|
||
-- 訊息基本資料
|
||
turn_number INTEGER NOT NULL,
|
||
sender_type VARCHAR(10) NOT NULL, -- user, ai
|
||
message_type VARCHAR(20) DEFAULT 'text', -- text, audio, system
|
||
|
||
-- 訊息內容
|
||
message_text TEXT,
|
||
audio_url TEXT,
|
||
metadata JSONB, -- 音調、語速等音頻元資料
|
||
|
||
-- AI分析結果 (僅用戶訊息)
|
||
analysis_result JSONB,
|
||
corrections JSONB,
|
||
suggestions JSONB,
|
||
|
||
-- 回應時間 (僅用戶訊息)
|
||
response_time DECIMAL(5,3), -- seconds
|
||
|
||
-- 系統欄位
|
||
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
|
||
CONSTRAINT msg_sender_check CHECK (sender_type IN ('user', 'ai')),
|
||
CONSTRAINT msg_type_check CHECK (message_type IN ('text', 'audio', 'system'))
|
||
);
|
||
|
||
-- 索引
|
||
CREATE INDEX idx_dialogue_messages_dialogue ON dialogue_messages(dialogue_id);
|
||
CREATE INDEX idx_dialogue_messages_turn ON dialogue_messages(dialogue_id, turn_number);
|
||
```
|
||
|
||
### 5. 任務系統表 (Task & TaskReward)
|
||
|
||
```sql
|
||
CREATE TABLE tasks (
|
||
task_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
task_code VARCHAR(100) UNIQUE NOT NULL,
|
||
|
||
-- 任務基本資訊
|
||
task_type VARCHAR(20) NOT NULL, -- daily, weekly, monthly, achievement, special
|
||
title VARCHAR(200) NOT NULL,
|
||
description TEXT,
|
||
long_description TEXT,
|
||
|
||
-- 任務條件
|
||
objectives JSONB NOT NULL, -- 任務目標配置
|
||
requirements JSONB DEFAULT '{}', -- 完成條件
|
||
difficulty VARCHAR(20) DEFAULT 'easy', -- easy, medium, hard, expert
|
||
|
||
-- 獎勵設定
|
||
rewards JSONB NOT NULL, -- 經驗值、積分、道具等獎勵
|
||
|
||
-- 時間設定
|
||
duration_hours INTEGER, -- 任務持續時間
|
||
cooldown_hours INTEGER, -- 重置冷卻時間
|
||
|
||
-- 狀態與統計
|
||
status VARCHAR(20) DEFAULT 'active',
|
||
completion_rate DECIMAL(5,4) DEFAULT 0.0000,
|
||
total_attempts INTEGER DEFAULT 0,
|
||
total_completions INTEGER DEFAULT 0,
|
||
|
||
-- 系統欄位
|
||
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
||
|
||
CONSTRAINT tasks_type_check CHECK (task_type IN ('daily', 'weekly', 'monthly', 'achievement', 'special')),
|
||
CONSTRAINT tasks_difficulty_check CHECK (difficulty IN ('easy', 'medium', 'hard', 'expert'))
|
||
);
|
||
|
||
CREATE TABLE user_tasks (
|
||
user_task_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id UUID REFERENCES users(user_id) ON DELETE CASCADE,
|
||
task_id UUID REFERENCES tasks(task_id) ON DELETE CASCADE,
|
||
|
||
-- 進度追蹤
|
||
status VARCHAR(20) DEFAULT 'in_progress', -- in_progress, completed, expired, abandoned
|
||
progress JSONB DEFAULT '{}', -- 各目標的完成進度
|
||
progress_percentage DECIMAL(5,2) DEFAULT 0.00,
|
||
|
||
-- 時間記錄
|
||
assigned_at TIMESTAMPTZ DEFAULT NOW(),
|
||
started_at TIMESTAMPTZ,
|
||
completed_at TIMESTAMPTZ,
|
||
expires_at TIMESTAMPTZ,
|
||
|
||
-- 獎勵狀態
|
||
reward_claimed BOOLEAN DEFAULT FALSE,
|
||
reward_claimed_at TIMESTAMPTZ,
|
||
|
||
CONSTRAINT user_tasks_status_check CHECK (status IN ('in_progress', 'completed', 'expired', 'abandoned')),
|
||
UNIQUE(user_id, task_id, assigned_at::date) -- 防止同日重複分配
|
||
);
|
||
|
||
-- 索引
|
||
CREATE INDEX idx_tasks_type ON tasks(task_type);
|
||
CREATE INDEX idx_tasks_status ON tasks(status);
|
||
CREATE INDEX idx_user_tasks_user ON user_tasks(user_id);
|
||
CREATE INDEX idx_user_tasks_status ON user_tasks(status);
|
||
CREATE INDEX idx_user_tasks_expires ON user_tasks(expires_at) WHERE status = 'in_progress';
|
||
```
|
||
|
||
### 6. 排行榜系統 (Leaderboard)
|
||
|
||
```sql
|
||
CREATE TABLE leaderboards (
|
||
leaderboard_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
leaderboard_type VARCHAR(50) NOT NULL, -- global, weekly, monthly, friends, regional
|
||
category VARCHAR(50) NOT NULL, -- overall_score, dialogue_count, vocabulary_mastered, streak
|
||
|
||
-- 時間範圍
|
||
period_type VARCHAR(20) NOT NULL, -- all_time, daily, weekly, monthly, yearly
|
||
period_start DATE,
|
||
period_end DATE,
|
||
|
||
-- 地區設定 (針對regional類型)
|
||
region_code VARCHAR(10),
|
||
|
||
-- 狀態
|
||
status VARCHAR(20) DEFAULT 'active',
|
||
last_updated TIMESTAMPTZ DEFAULT NOW(),
|
||
|
||
-- 系統欄位
|
||
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
|
||
CONSTRAINT lb_type_check CHECK (leaderboard_type IN ('global', 'weekly', 'monthly', 'friends', 'regional')),
|
||
CONSTRAINT lb_category_check CHECK (category IN ('overall_score', 'dialogue_count', 'vocabulary_mastered', 'streak')),
|
||
CONSTRAINT lb_period_check CHECK (period_type IN ('all_time', 'daily', 'weekly', 'monthly', 'yearly'))
|
||
);
|
||
|
||
CREATE TABLE leaderboard_entries (
|
||
entry_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
leaderboard_id UUID REFERENCES leaderboards(leaderboard_id) ON DELETE CASCADE,
|
||
user_id UUID REFERENCES users(user_id) ON DELETE CASCADE,
|
||
|
||
-- 排名資料
|
||
rank_position INTEGER NOT NULL,
|
||
score INTEGER NOT NULL,
|
||
previous_rank INTEGER,
|
||
rank_change INTEGER DEFAULT 0,
|
||
|
||
-- 詳細統計 (依leaderboard category而定)
|
||
detailed_stats JSONB DEFAULT '{}',
|
||
|
||
-- 時間戳記
|
||
calculated_at TIMESTAMPTZ DEFAULT NOW(),
|
||
|
||
UNIQUE(leaderboard_id, user_id),
|
||
UNIQUE(leaderboard_id, rank_position)
|
||
);
|
||
|
||
-- 索引
|
||
CREATE INDEX idx_leaderboards_type_category ON leaderboards(leaderboard_type, category);
|
||
CREATE INDEX idx_lb_entries_leaderboard ON leaderboard_entries(leaderboard_id);
|
||
CREATE INDEX idx_lb_entries_rank ON leaderboard_entries(leaderboard_id, rank_position);
|
||
CREATE INDEX idx_lb_entries_user ON leaderboard_entries(user_id);
|
||
```
|
||
|
||
### 7. 訂閱與付費 (Subscription & Purchase)
|
||
|
||
```sql
|
||
CREATE TABLE subscription_plans (
|
||
plan_id VARCHAR(50) PRIMARY KEY,
|
||
plan_name VARCHAR(100) NOT NULL,
|
||
description TEXT,
|
||
|
||
-- 定價
|
||
monthly_price INTEGER, -- 以分為單位 (TWD)
|
||
yearly_price INTEGER,
|
||
currency VARCHAR(3) DEFAULT 'TWD',
|
||
|
||
-- 功能權限
|
||
features JSONB NOT NULL,
|
||
limitations JSONB DEFAULT '{}',
|
||
|
||
-- 狀態
|
||
status VARCHAR(20) DEFAULT 'active',
|
||
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ DEFAULT NOW()
|
||
);
|
||
|
||
CREATE TABLE subscriptions (
|
||
subscription_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id UUID REFERENCES users(user_id) ON DELETE CASCADE,
|
||
plan_id VARCHAR(50) REFERENCES subscription_plans(plan_id),
|
||
|
||
-- 訂閱狀態
|
||
status VARCHAR(20) NOT NULL DEFAULT 'active', -- active, expired, cancelled, paused
|
||
billing_cycle VARCHAR(20) NOT NULL, -- monthly, yearly
|
||
|
||
-- 時間管理
|
||
started_at TIMESTAMPTZ DEFAULT NOW(),
|
||
current_period_start TIMESTAMPTZ DEFAULT NOW(),
|
||
current_period_end TIMESTAMPTZ,
|
||
cancelled_at TIMESTAMPTZ,
|
||
expires_at TIMESTAMPTZ,
|
||
|
||
-- 付費資訊
|
||
amount INTEGER NOT NULL, -- 以分為單位
|
||
currency VARCHAR(3) DEFAULT 'TWD',
|
||
payment_method_id VARCHAR(100),
|
||
auto_renewal BOOLEAN DEFAULT TRUE,
|
||
|
||
-- 外部系統整合
|
||
external_subscription_id VARCHAR(100), -- Stripe等第三方訂閱ID
|
||
|
||
-- 系統欄位
|
||
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
||
|
||
CONSTRAINT sub_status_check CHECK (status IN ('active', 'expired', 'cancelled', 'paused')),
|
||
CONSTRAINT sub_billing_check CHECK (billing_cycle IN ('monthly', 'yearly'))
|
||
);
|
||
|
||
CREATE TABLE purchases (
|
||
purchase_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id UUID REFERENCES users(user_id) ON DELETE CASCADE,
|
||
|
||
-- 購買內容
|
||
content_type VARCHAR(50) NOT NULL, -- scenario_pack, expert_course, premium_content
|
||
content_id VARCHAR(100) NOT NULL,
|
||
content_title VARCHAR(200),
|
||
|
||
-- 付費資訊
|
||
amount INTEGER NOT NULL, -- 以分為單位
|
||
currency VARCHAR(3) DEFAULT 'TWD',
|
||
payment_status VARCHAR(20) DEFAULT 'pending', -- pending, completed, failed, refunded
|
||
payment_method VARCHAR(50),
|
||
|
||
-- 外部付費系統
|
||
external_payment_id VARCHAR(100), -- Stripe Payment Intent ID
|
||
payment_metadata JSONB DEFAULT '{}',
|
||
|
||
-- 時間記錄
|
||
purchased_at TIMESTAMPTZ DEFAULT NOW(),
|
||
refunded_at TIMESTAMPTZ,
|
||
|
||
-- 系統欄位
|
||
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
||
|
||
CONSTRAINT purchase_status_check CHECK (payment_status IN ('pending', 'completed', 'failed', 'refunded'))
|
||
);
|
||
|
||
-- 索引
|
||
CREATE INDEX idx_subscriptions_user ON subscriptions(user_id);
|
||
CREATE INDEX idx_subscriptions_status ON subscriptions(status);
|
||
CREATE INDEX idx_subscriptions_expires ON subscriptions(expires_at) WHERE status = 'active';
|
||
CREATE INDEX idx_purchases_user ON purchases(user_id);
|
||
CREATE INDEX idx_purchases_content ON purchases(content_type, content_id);
|
||
```
|
||
|
||
### 8. 廣告系統 (AdImpression)
|
||
|
||
```sql
|
||
CREATE TABLE ad_campaigns (
|
||
campaign_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
campaign_name VARCHAR(200) NOT NULL,
|
||
|
||
-- 廣告內容
|
||
ad_type VARCHAR(20) NOT NULL, -- video, banner, interstitial, rewarded
|
||
content_url TEXT,
|
||
duration INTEGER, -- seconds for video ads
|
||
|
||
-- 目標設定
|
||
target_audience JSONB DEFAULT '{}',
|
||
daily_budget INTEGER, -- 以分為單位
|
||
max_impressions_per_user INTEGER DEFAULT 5,
|
||
|
||
-- 獎勵設定 (針對rewarded ads)
|
||
reward_type VARCHAR(50),
|
||
reward_amount INTEGER,
|
||
|
||
-- 時間設定
|
||
starts_at TIMESTAMPTZ,
|
||
ends_at TIMESTAMPTZ,
|
||
|
||
-- 狀態
|
||
status VARCHAR(20) DEFAULT 'active',
|
||
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
|
||
CONSTRAINT ad_type_check CHECK (ad_type IN ('video', 'banner', 'interstitial', 'rewarded'))
|
||
);
|
||
|
||
CREATE TABLE ad_impressions (
|
||
impression_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
campaign_id UUID REFERENCES ad_campaigns(campaign_id) ON DELETE CASCADE,
|
||
user_id UUID REFERENCES users(user_id) ON DELETE CASCADE,
|
||
|
||
-- 展示資訊
|
||
impression_type VARCHAR(20) NOT NULL, -- impression, click, completion
|
||
ad_placement VARCHAR(50), -- home_screen, post_dialogue, reward_offer
|
||
|
||
-- 用戶行為
|
||
watch_duration INTEGER DEFAULT 0, -- seconds
|
||
completion_rate DECIMAL(5,4), -- 0.0000 - 1.0000
|
||
user_action VARCHAR(20), -- viewed, clicked, skipped, completed
|
||
|
||
-- 獎勵發放
|
||
reward_granted BOOLEAN DEFAULT FALSE,
|
||
reward_type VARCHAR(50),
|
||
reward_amount INTEGER,
|
||
|
||
-- 系統資訊
|
||
user_agent TEXT,
|
||
ip_address INET,
|
||
country_code CHAR(2),
|
||
|
||
-- 時間記錄
|
||
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
|
||
CONSTRAINT impression_type_check CHECK (impression_type IN ('impression', 'click', 'completion')),
|
||
CONSTRAINT user_action_check CHECK (user_action IN ('viewed', 'clicked', 'skipped', 'completed'))
|
||
);
|
||
|
||
-- 索引
|
||
CREATE INDEX idx_ad_impressions_campaign ON ad_impressions(campaign_id);
|
||
CREATE INDEX idx_ad_impressions_user ON ad_impressions(user_id);
|
||
CREATE INDEX idx_ad_impressions_date ON ad_impressions(created_at);
|
||
CREATE INDEX idx_ad_impressions_reward ON ad_impressions(reward_granted) WHERE reward_granted = TRUE;
|
||
```
|
||
|
||
### 9. 詞彙系統擴展
|
||
|
||
```sql
|
||
CREATE TABLE vocabulary_bank (
|
||
vocab_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
||
-- 基本詞彙資訊
|
||
word VARCHAR(100) NOT NULL,
|
||
phonetic VARCHAR(200),
|
||
part_of_speech VARCHAR(50),
|
||
|
||
-- 定義與翻譯
|
||
definition_en TEXT,
|
||
definition_native JSONB, -- 支援多語言翻譯
|
||
|
||
-- 分類與分級
|
||
category VARCHAR(50), -- life, academic, business, etc.
|
||
subcategory VARCHAR(50),
|
||
difficulty_level VARCHAR(10), -- A1-C2
|
||
frequency_rank INTEGER,
|
||
|
||
-- 學習輔助
|
||
etymology TEXT, -- 詞源
|
||
memory_tips JSONB, -- 記憶提示
|
||
collocations JSONB, -- 常用搭配
|
||
synonyms JSONB, -- 同義詞
|
||
antonyms JSONB, -- 反義詞
|
||
|
||
-- 多媒體資源
|
||
audio_url TEXT,
|
||
image_url TEXT,
|
||
example_sentences JSONB,
|
||
|
||
-- 使用統計
|
||
usage_frequency INTEGER DEFAULT 0,
|
||
learning_difficulty DECIMAL(3,2) DEFAULT 5.00, -- 1.00-10.00
|
||
|
||
-- 系統欄位
|
||
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
||
|
||
UNIQUE(word, part_of_speech)
|
||
);
|
||
|
||
CREATE TABLE user_vocabulary_progress (
|
||
progress_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id UUID REFERENCES users(user_id) ON DELETE CASCADE,
|
||
vocab_id UUID REFERENCES vocabulary_bank(vocab_id) ON DELETE CASCADE,
|
||
|
||
-- 掌握度狀態
|
||
mastery_level VARCHAR(20) DEFAULT 'learning', -- learning, practicing, mastered, forgotten
|
||
mastery_score INTEGER DEFAULT 0, -- 0-100
|
||
|
||
-- 複習資料
|
||
review_count INTEGER DEFAULT 0,
|
||
correct_count INTEGER DEFAULT 0,
|
||
incorrect_count INTEGER DEFAULT 0,
|
||
|
||
-- 間隔複習演算法
|
||
ease_factor DECIMAL(4,2) DEFAULT 2.50, -- SuperMemo算法參數
|
||
interval_days INTEGER DEFAULT 1,
|
||
next_review_date DATE,
|
||
last_review_quality INTEGER, -- 1-5
|
||
|
||
-- 時間記錄
|
||
first_encountered TIMESTAMPTZ DEFAULT NOW(),
|
||
last_reviewed TIMESTAMPTZ,
|
||
mastered_at TIMESTAMPTZ,
|
||
|
||
-- 學習情境
|
||
encountered_in_lessons JSONB DEFAULT '[]',
|
||
encountered_in_dialogues JSONB DEFAULT '[]',
|
||
|
||
UNIQUE(user_id, vocab_id)
|
||
);
|
||
|
||
-- 索引
|
||
CREATE INDEX idx_vocabulary_word ON vocabulary_bank(word);
|
||
CREATE INDEX idx_vocabulary_category ON vocabulary_bank(category, difficulty_level);
|
||
CREATE INDEX idx_vocabulary_frequency ON vocabulary_bank(frequency_rank);
|
||
CREATE INDEX idx_user_vocab_user ON user_vocabulary_progress(user_id);
|
||
CREATE INDEX idx_user_vocab_mastery ON user_vocabulary_progress(mastery_level);
|
||
CREATE INDEX idx_user_vocab_review ON user_vocabulary_progress(next_review_date) WHERE next_review_date IS NOT NULL;
|
||
```
|
||
|
||
## 關聯表和輔助表
|
||
|
||
### 用戶關係表 (Friends/Social)
|
||
|
||
```sql
|
||
CREATE TABLE user_relationships (
|
||
relationship_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
requester_id UUID REFERENCES users(user_id) ON DELETE CASCADE,
|
||
addressee_id UUID REFERENCES users(user_id) ON DELETE CASCADE,
|
||
|
||
relationship_type VARCHAR(20) DEFAULT 'friend', -- friend, blocked, follow
|
||
status VARCHAR(20) DEFAULT 'pending', -- pending, accepted, rejected
|
||
|
||
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
||
|
||
UNIQUE(requester_id, addressee_id),
|
||
CONSTRAINT no_self_relationship CHECK (requester_id != addressee_id)
|
||
);
|
||
```
|
||
|
||
### 學習會話表 (Study Sessions)
|
||
|
||
```sql
|
||
CREATE TABLE study_sessions (
|
||
session_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id UUID REFERENCES users(user_id) ON DELETE CASCADE,
|
||
|
||
-- 會話資料
|
||
session_type VARCHAR(20) DEFAULT 'dialogue', -- dialogue, vocabulary, review
|
||
started_at TIMESTAMPTZ DEFAULT NOW(),
|
||
ended_at TIMESTAMPTZ,
|
||
duration_seconds INTEGER,
|
||
|
||
-- 學習成果
|
||
activities_completed INTEGER DEFAULT 0,
|
||
total_score INTEGER DEFAULT 0,
|
||
experience_gained INTEGER DEFAULT 0,
|
||
|
||
-- 詳細活動記錄
|
||
activity_log JSONB DEFAULT '[]',
|
||
|
||
created_at TIMESTAMPTZ DEFAULT NOW()
|
||
);
|
||
```
|
||
|
||
### 系統設定表
|
||
|
||
```sql
|
||
CREATE TABLE system_settings (
|
||
setting_key VARCHAR(100) PRIMARY KEY,
|
||
setting_value JSONB,
|
||
setting_type VARCHAR(20) DEFAULT 'string', -- string, number, boolean, json
|
||
description TEXT,
|
||
is_public BOOLEAN DEFAULT FALSE, -- 是否對前端公開
|
||
updated_at TIMESTAMPTZ DEFAULT NOW()
|
||
);
|
||
|
||
-- 預設設定
|
||
INSERT INTO system_settings (setting_key, setting_value, setting_type, description, is_public) VALUES
|
||
('app_version', '"1.0.0"', 'string', 'Current app version', true),
|
||
('maintenance_mode', 'false', 'boolean', 'System maintenance status', true),
|
||
('max_daily_dialogues_free', '5', 'number', 'Daily dialogue limit for free users', false),
|
||
('vocabulary_review_batch_size', '15', 'number', 'Number of words per review session', false);
|
||
```
|
||
|
||
## 資料庫最佳化
|
||
|
||
### 索引策略
|
||
|
||
```sql
|
||
-- 複合索引
|
||
CREATE INDEX idx_dialogues_user_completed ON dialogues(user_id, completed_at) WHERE completed_at IS NOT NULL;
|
||
CREATE INDEX idx_user_tasks_user_status ON user_tasks(user_id, status);
|
||
CREATE INDEX idx_user_vocab_user_review ON user_vocabulary_progress(user_id, next_review_date) WHERE next_review_date <= CURRENT_DATE;
|
||
|
||
-- 部分索引
|
||
CREATE INDEX idx_active_users ON users(last_login_at) WHERE status = 'active';
|
||
CREATE INDEX idx_premium_lessons ON lessons(lesson_id) WHERE is_premium = TRUE;
|
||
|
||
-- 函數索引
|
||
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
|
||
CREATE INDEX idx_lessons_title_search ON lessons USING gin(to_tsvector('english', title));
|
||
```
|
||
|
||
### 分區策略
|
||
|
||
```sql
|
||
-- 按日期分區對話記錄表 (適用於大量數據)
|
||
CREATE TABLE dialogues_partitioned (
|
||
LIKE dialogues INCLUDING ALL
|
||
) PARTITION BY RANGE (created_at);
|
||
|
||
-- 創建分區
|
||
CREATE TABLE dialogues_2024_q1 PARTITION OF dialogues_partitioned
|
||
FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
|
||
```
|
||
|
||
### 視圖 (Views)
|
||
|
||
```sql
|
||
-- 用戶學習統計視圖
|
||
CREATE VIEW user_learning_stats AS
|
||
SELECT
|
||
u.user_id,
|
||
u.username,
|
||
COUNT(d.dialogue_id) as total_dialogues,
|
||
AVG(d.overall_score)::INTEGER as average_score,
|
||
SUM(d.total_duration) as total_study_time,
|
||
COUNT(DISTINCT d.lesson_id) as unique_lessons_practiced,
|
||
MAX(d.completed_at) as last_practice_date
|
||
FROM users u
|
||
LEFT JOIN dialogues d ON u.user_id = d.user_id AND d.status = 'completed'
|
||
GROUP BY u.user_id, u.username;
|
||
|
||
-- 排行榜視圖
|
||
CREATE VIEW global_leaderboard AS
|
||
SELECT
|
||
ROW_NUMBER() OVER (ORDER BY total_score DESC) as rank,
|
||
user_id,
|
||
username,
|
||
total_score,
|
||
current_streak,
|
||
country_code
|
||
FROM users
|
||
WHERE status = 'active'
|
||
ORDER BY total_score DESC
|
||
LIMIT 1000;
|
||
```
|
||
|
||
## 資料遷移與版本控制
|
||
|
||
### 遷移腳本範例
|
||
|
||
```sql
|
||
-- Migration: 001_create_initial_schema.sql
|
||
-- 創建基本表結構
|
||
|
||
-- Migration: 002_add_vocabulary_system.sql
|
||
-- 新增詞彙系統相關表
|
||
|
||
-- Migration: 003_add_gamification_features.sql
|
||
-- 新增遊戲化功能表
|
||
```
|
||
|
||
### 種子資料 (Seed Data)
|
||
|
||
```sql
|
||
-- 插入基本課程場景
|
||
INSERT INTO lessons (lesson_code, title, description, category, difficulty_level, scenario_data) VALUES
|
||
('SC_Greeting_01', 'Basic Greetings', 'Learn how to greet people in different situations', 'daily_life', 'A1',
|
||
'{"scenes": [{"type": "greeting", "context": "meeting_stranger"}]}'),
|
||
('SC_Restaurant_01', 'Restaurant Reservation', 'Make a reservation and order food', 'daily_life', 'A2',
|
||
'{"scenes": [{"type": "phone_call", "context": "restaurant_booking"}]}');
|
||
|
||
-- 插入詞彙庫基礎資料
|
||
INSERT INTO vocabulary_bank (word, phonetic, part_of_speech, definition_en, definition_native, category, difficulty_level, frequency_rank) VALUES
|
||
('hello', '/həˈloʊ/', 'interjection', 'Used as a greeting', '{"zh-TW": "你好", "ja": "こんにちは"}', 'daily_interaction', 'A1', 50),
|
||
('restaurant', '/ˈrestərɑːnt/', 'noun', 'A place where people pay to sit and eat meals', '{"zh-TW": "餐廳", "ja": "レストラン"}', 'food_dining', 'A2', 1250);
|
||
```
|
||
|
||
## 備份與災難復原
|
||
|
||
### 備份策略
|
||
|
||
```sql
|
||
-- 每日備份腳本
|
||
pg_dump dramaling_db > backup_$(date +%Y%m%d).sql
|
||
|
||
-- 增量備份設定
|
||
archive_mode = on
|
||
archive_command = 'cp %p /backup/archive/%f'
|
||
```
|
||
|
||
### 監控查詢
|
||
|
||
```sql
|
||
-- 慢查詢監控
|
||
SELECT query, mean_time, calls, total_time
|
||
FROM pg_stat_statements
|
||
ORDER BY mean_time DESC
|
||
LIMIT 10;
|
||
|
||
-- 資料庫大小監控
|
||
SELECT schemaname, tablename,
|
||
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
|
||
FROM pg_tables
|
||
WHERE schemaname NOT IN ('information_schema', 'pg_catalog')
|
||
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
|
||
```
|
||
|
||
---
|
||
|
||
## 待完成任務
|
||
|
||
### 高優先級
|
||
1. [ ] 完善所有表的約束條件和觸發器
|
||
2. [ ] 設計詳細的索引優化策略
|
||
3. [ ] 建立資料庫遷移和版本控制流程
|
||
4. [ ] 實現資料備份和災難復原機制
|
||
|
||
### 中優先級
|
||
1. [ ] 設計資料庫效能監控和告警系統
|
||
2. [ ] 建立資料隱私和安全性控制機制
|
||
3. [ ] 規劃大數據量情況下的分區和分片策略
|
||
4. [ ] 設計資料倉儲和分析資料庫架構
|
||
|
||
### 低優先級
|
||
1. [ ] 研究NoSQL資料庫的混合使用場景
|
||
2. [ ] 探索時序資料庫在學習分析中的應用
|
||
3. [ ] 建立自動化資料庫調優和維護系統
|
||
4. [ ] 設計多地區資料同步和一致性策略
|
||
|
||
---
|
||
|
||
**最後更新**: 2024年9月5日
|
||
**負責人**: 待分配
|
||
**審查週期**: 每兩週檢討一次 |