# Sơ Đồ Kiến Trúc Multi-Tenant - Database Per Tenant

## 1. Kiến Trúc Tổng Quan

```mermaid
graph TB
    subgraph "Client Layer"
        A[Admin Browser]
        S[Student Browser]
    end
    
    subgraph "Application Layer"
        LB[Load Balancer]
        APP1[App Server 1]
        APP2[App Server 2]
        MW[Tenant Middleware]
    end
    
    subgraph "Database Layer"
        DB_CONFIG[(lms_new<br/>Config DB)]
        DB_T1[(tenant_icc)]
        DB_T2[(tenant_abc)]
        DB_T3[(tenant_xyz)]
    end
    
    A --> LB
    S --> LB
    LB --> APP1
    LB --> APP2
    APP1 --> MW
    APP2 --> MW
    MW --> DB_CONFIG
    MW --> DB_T1
    MW --> DB_T2
    MW --> DB_T3
```

## 2. Luồng Đăng Nhập Admin

```mermaid
sequenceDiagram
    participant Admin
    participant App
    participant lms_new
    participant TenantDB
    
    Admin->>App: POST /login (email, password)
    App->>lms_new: SELECT * FROM users WHERE email=?
    lms_new-->>App: User data
    App->>lms_new: SELECT * FROM user_tenant_permissions WHERE user_id=?
    lms_new-->>App: List of tenants
    App-->>Admin: Show tenant selection UI
    Admin->>App: Select tenant (e.g., tenant_icc)
    App->>App: Switch DB connection to tenant_icc
    App->>TenantDB: All subsequent queries
    TenantDB-->>App: Tenant data
    App-->>Admin: Dashboard with tenant data
```

## 3. Luồng Request Student

```mermaid
sequenceDiagram
    participant Student
    participant Frontend
    participant App
    participant TenantDB
    
    Student->>Frontend: Access https://icc.lms.com
    Frontend->>App: Request with subdomain=icc
    App->>App: Detect tenant from subdomain
    App->>App: Switch DB to tenant_icc
    App->>TenantDB: SELECT * FROM students WHERE id=?
    TenantDB-->>App: Student data
    App->>TenantDB: SELECT * FROM exam_histories WHERE student_id=?
    TenantDB-->>App: Exam histories
    App-->>Frontend: Student dashboard
    Frontend-->>Student: Display data
```

## 4. Cấu Trúc Database Schema

### 4.1. Database lms_new (Config)

```mermaid
erDiagram
    tenants ||--o{ user_tenant_permissions : has
    users ||--o{ user_tenant_permissions : has
    
    tenants {
        int id PK
        string name UK
        string database_name
        string status
        datetime created_at
        datetime updated_at
    }
    
    users {
        int id PK
        string email UK
        string password
        string name
        string role
        datetime created_at
        datetime updated_at
    }
    
    user_tenant_permissions {
        int id PK
        int user_id FK
        int tenant_id FK
        json permissions
        string role_in_tenant
        datetime created_at
        datetime updated_at
    }
```

### 4.2. Database tenant_xxx (Business Data)

```mermaid
erDiagram
    students ||--o{ exam_histories : has
    students ||--o{ enrollments : has
    courses ||--o{ enrollments : has
    courses ||--o{ lessons : has
    lessons ||--o{ questions : has
    
    students {
        int id PK
        string name
        string email
        string phone
        datetime created_at
        datetime updated_at
    }
    
    exam_histories {
        int id PK
        int student_id FK
        int exam_id FK
        float score
        datetime completed_at
        datetime created_at
    }
    
    courses {
        int id PK
        string name
        string description
        datetime created_at
        datetime updated_at
    }
    
    enrollments {
        int id PK
        int student_id FK
        int course_id FK
        datetime enrolled_at
        string status
    }
    
    lessons {
        int id PK
        int course_id FK
        string title
        string content
        int order
    }
    
    questions {
        int id PK
        int lesson_id FK
        string question_text
        string type
        json options
    }
```

## 5. Connection Switching Flow

```mermaid
graph LR
    A[Request Arrives] --> B{Detect Tenant}
    B -->|Admin| C[Check Session]
    B -->|Student| D[Parse Subdomain/Path]
    C --> E[Get Tenant from Session]
    D --> F[Map to Tenant DB Name]
    E --> G[Switch DB Connection]
    F --> G
    G --> H[Execute Query]
    H --> I[Return Results]
```

## 6. So Sánh Kiến Trúc

### 6.1. Database Per Tenant (Hiện tại)

```mermaid
graph TB
    subgraph "App Server"
        APP[Application]
    end
    
    subgraph "Database Per Tenant"
        DB1[(tenant_icc<br/>100GB)]
        DB2[(tenant_abc<br/>50GB)]
        DB3[(tenant_xyz<br/>200GB)]
    end
    
    APP --> DB1
    APP --> DB2
    APP --> DB3
    
    style DB1 fill:#90EE90
    style DB2 fill:#90EE90
    style DB3 fill:#90EE90
```

**Đặc điểm:**
- Mỗi tenant = 1 DB riêng
- Isolation hoàn toàn
- Scale độc lập
- Chi phí cao

### 6.2. Shared Database (So sánh)

```mermaid
graph TB
    subgraph "App Server"
        APP[Application]
    end
    
    subgraph "Shared Database"
        DB[(lms_shared<br/>350GB<br/>All Tenants)]
    end
    
    APP --> DB
    
    style DB fill:#FFB6C1
```

**Đặc điểm:**
- Tất cả tenant = 1 DB chung
- Phân biệt bằng tenant_id
- Chi phí thấp
- Isolation logic

## 7. Performance Comparison

### 7.1. Query Performance

```
Database Per Tenant:
  SELECT * FROM students WHERE id = 1
  → Query trực tiếp, không cần filter tenant_id
  → Index nhỏ hơn, query nhanh hơn
  
Shared Database:
  SELECT * FROM students WHERE tenant_id = 1 AND id = 1
  → Phải filter tenant_id mọi lúc
  → Index lớn hơn, query chậm hơn
```

### 7.2. Scalability

```
Database Per Tenant:
  tenant_icc (lớn) → Scale riêng DB lớn
  tenant_abc (nhỏ) → Giữ nguyên DB nhỏ
  → Scale linh hoạt, không lãng phí
  
Shared Database:
  Tất cả tenant → Scale chung
  → Phải scale cho tenant lớn nhất
  → Lãng phí cho tenant nhỏ
```

## 8. Security Comparison

### 8.1. Database Per Tenant

```
✅ Physical isolation
✅ Không thể query nhầm tenant
✅ Backup riêng biệt
✅ Compliance dễ dàng
```

### 8.2. Shared Database

```
⚠️ Logic isolation (dựa vào code)
⚠️ Rủi ro quên filter tenant_id
⚠️ SQL injection có thể leak data
⚠️ Backup phải filter tenant_id
```

## 9. Cost Analysis

### 9.1. Database Per Tenant

```
Chi phí = N × (DB Instance Cost)
N = số lượng tenant

Ví dụ:
- 10 tenants × $50/tháng = $500/tháng
- 100 tenants × $50/tháng = $5,000/tháng
```

### 9.2. Shared Database

```
Chi phí = 1 × (DB Instance Cost)

Ví dụ:
- 1 DB × $200/tháng = $200/tháng
- (Scale theo size, không theo số tenant)
```

## 10. Migration Strategy

```mermaid
graph TB
    A[Schema Change] --> B[Create Migration Script]
    B --> C[Test on Dev Tenant]
    C --> D{Success?}
    D -->|No| E[Fix Issues]
    E --> C
    D -->|Yes| F[Deploy to All Tenants]
    F --> G[Monitor Each Tenant]
    G --> H{All Success?}
    H -->|No| I[Rollback Failed Tenants]
    H -->|Yes| J[Complete]
    I --> K[Fix and Retry]
    K --> F
```