# Database Visual Overview

## 📊 Database Architecture

```
┌─────────────────────────────────────────────────────┐
│         Government Travel Cost Estimator            │
│                   (Web Application)                 │
└──────────────────┬──────────────────────────────────┘
                   │
        ┌──────────┴──────────┐
        │                     │
        ▼                     ▼
┌──────────────┐      ┌──────────────┐
│ perDiem      │      │accommodation │
│ Rates.json   │      │ Rates.json   │
└──────────────┘      └──────────────┘
```

## 🗂️ Per Diem Database Structure

```
perDiemRates.json
│
├── metadata
│   ├── effectiveDate: "2025-10-01"
│   ├── version: "1.0"
│   ├── lastUpdated: "2025-10-30"
│   └── source: "NJC Travel Directive"
│
├── regions
│   │
│   ├── canada
│   │   ├── meals
│   │   │   ├── breakfast (rate100: 29.05, rate75: 21.80, rate50: 14.55)
│   │   │   ├── lunch (rate100: 29.60, rate75: 22.20, rate50: 14.80)
│   │   │   └── dinner (rate100: 60.75, rate75: 45.55, rate50: 30.40)
│   │   ├── incidentals (rate100: 17.30, rate75: 13.00)
│   │   └── privateAccommodation (50.00/night)
│   │
│   ├── yukon (similar structure)
│   ├── nwt (similar structure)
│   ├── nunavut (similar structure)
│   ├── usa (similar structure)
│   ├── alaska (similar structure)
│   └── international (similar structure)
│
└── rateRules
    ├── day1to30: "rate100"
    ├── day31to120: "rate75"
    └── day121onward: "rate50"
```

## 🏨 Accommodation Database Structure

```
accommodationRates.json
│
├── metadata
│   ├── effectiveDate: "2025-10-30"
│   ├── version: "1.0"
│   └── source: "PWGSC Accommodation Directory"
│
├── cities (Canadian & US)
│   ├── ottawa (standard: 165, max: 200, CAD)
│   ├── toronto (standard: 180, max: 220, CAD)
│   ├── vancouver (standard: 190, max: 240, CAD)
│   ├── newyork (standard: 250, max: 350, USD)
│   └── ... (30+ cities)
│
├── internationalCities
│   ├── london (standard: 280, max: 380, CAD)
│   ├── paris (standard: 260, max: 350, CAD)
│   ├── tokyo (standard: 240, max: 340, CAD)
│   └── ... (8 cities)
│
└── defaults (by region)
    ├── canada (150/185 CAD)
    ├── usa (150/200 USD)
    ├── yukon (185/230 CAD)
    └── ... (7 regions)
```

## 🔄 Data Flow Diagram

```
┌────────────────┐
│  User Inputs   │
│  - Departure   │
│  - Destination │
│  - Dates       │
└───────┬────────┘
        │
        ▼
┌────────────────────┐
│  JavaScript        │
│  - loadDatabases() │
└───────┬────────────┘
        │
        ├─────────────────────┐
        │                     │
        ▼                     ▼
┌──────────────┐      ┌──────────────┐
│Load perDiem  │      │Load accom.   │
│Database      │      │Database      │
└───────┬──────┘      └───────┬──────┘
        │                     │
        ▼                     ▼
┌──────────────┐      ┌──────────────┐
│Get Region    │      │Find City     │
│Rates         │      │Rates         │
└───────┬──────┘      └───────┬──────┘
        │                     │
        └─────────┬───────────┘
                  │
                  ▼
        ┌──────────────────┐
        │Calculate Costs   │
        │- Meals           │
        │- Incidentals     │
        │- Accommodation   │
        │- Flight          │
        └────────┬─────────┘
                 │
                 ▼
        ┌──────────────────┐
        │Display Results   │
        │- Total Cost      │
        │- Breakdown       │
        │- Policy Links    │
        └──────────────────┘
```

## 📋 Rate Lookup Logic

### Per Diem Lookup
```
User selects destination type: "canada"
                ↓
Look up: perDiemRatesDB.regions["canada"]
                ↓
Extract:
├── Breakfast: $29.05
├── Lunch: $29.60
├── Dinner: $60.75
└── Incidental: $17.30
                ↓
Calculate: Daily Total = $136.70
```

### Accommodation Lookup
```
User enters city: "Toronto"
                ↓
Normalize: "toronto" (lowercase)
                ↓
Check: accommodationRatesDB.cities["toronto"]
                ↓
Found! Return:
├── Standard Rate: $180
├── Max Rate: $220
└── Suggestion: "Toronto, ON: $180-$220 CAD"
                ↓
If NOT found → Use regional default
```

## 🎯 Rate Tiers Visual

```
┌────────────────────────────────────────────────┐
│           Extended Stay Rate Tiers             │
├────────────────────────────────────────────────┤
│                                                │
│  Days 1-30    ████████████ 100% (rate100)     │
│               Full meal allowance              │
│                                                │
│  Days 31-120  ████████     75% (rate75)       │
│               Reduced meal allowance           │
│                                                │
│  Days 121+    █████        50% (rate50)       │
│               Further reduced meals            │
│               (Incidentals stay at 75%)        │
│                                                │
└────────────────────────────────────────────────┘
```

## 💰 Cost Calculation Formula

```
Total Travel Cost = Flight + Accommodation + Meals + Incidentals

Where:
├── Flight = Base Cost × Business Class Multiplier (if ≥9 hours)
├── Accommodation = Per Night Rate × Number of Nights
├── Meals = (Breakfast + Lunch + Dinner) × Number of Days
└── Incidentals = Daily Rate × Number of Days

Example:
├── Flight: $650 × 2.5 = $1,625 (10-hour flight → business class)
├── Accommodation: $180 × 3 nights = $540
├── Meals: $119.40 × 4 days = $477.60
└── Incidentals: $17.30 × 4 days = $69.20
─────────────────────────────────────────────────
Total: $2,711.80 CAD
```

## 🗺️ Region Coverage Map

```
┌──────────────────────────────────────────┐
│         CANADA                           │
│  ┌────────────────────────────────────┐  │
│  │ Yukon        NWT      Nunavut      │  │
│  │ $155.70    $159.05    $194.40     │  │
│  └────────────────────────────────────┘  │
│                                          │
│  ┌────────────────────────────────────┐  │
│  │ Provinces (Canada)                 │  │
│  │ Daily Total: $136.70 CAD           │  │
│  └────────────────────────────────────┘  │
└──────────────────────────────────────────┘

┌──────────────────────────────────────────┐
│         UNITED STATES                    │
│  ┌────────────────────────────────────┐  │
│  │ Alaska                             │  │
│  │ $155.70 USD                        │  │
│  └────────────────────────────────────┘  │
│                                          │
│  ┌────────────────────────────────────┐  │
│  │ Continental USA                    │  │
│  │ Daily Total: $136.70 USD           │  │
│  └────────────────────────────────────┘  │
└──────────────────────────────────────────┘

┌──────────────────────────────────────────┐
│         INTERNATIONAL                    │
│  Daily Total: $180.00 CAD (average)      │
│  (Varies by country - see Appendix D)    │
└──────────────────────────────────────────┘
```

## 📊 Database Size Comparison

```
File Size Visual:
┌────────────────────────────┐
│ perDiemRates.json          │
│ ████ ~4KB                  │
└────────────────────────────┘

┌────────────────────────────┐
│ accommodationRates.json    │
│ ██████ ~6KB                │
└────────────────────────────┘

┌────────────────────────────┐
│ script.js                  │
│ █████████ ~8.5KB           │
└────────────────────────────┘

┌────────────────────────────┐
│ styles.css                 │
│ ███████ ~6.7KB             │
└────────────────────────────┘

Total Project: ~30KB (very lightweight!)
```

## 🔄 Update Frequency Timeline

```
┌─────────────────────────────────────────────────┐
│                 Annual Cycle                    │
├─────────────────────────────────────────────────┤
│                                                 │
│  Jan   Feb   Mar   Apr   May   Jun             │
│   │     │     │     │     │     │              │
│   └─────┴─────┴─────┴─────┴─────┘              │
│         Monitor for updates                     │
│                                                 │
│  Jul   Aug   Sep  [OCT]  Nov   Dec             │
│   │     │     │   ╔═══╗   │     │              │
│   │     │     │   ║NEW║   │     │              │
│   │     │     │   ║RATES                       │
│   │     │     │   ╚═══╝   │     │              │
│   └─────┴─────┴────────┴──┴─────┘              │
│                Update DB!                       │
│                                                 │
│  Typical effective date: October 1st           │
│  Update databases immediately after release    │
│                                                 │
└─────────────────────────────────────────────────┘
```

## 🎨 JSON Structure Colors (Conceptual)

```
{
  "metadata": { ... }        ← 🔵 Blue (Info)
  "regions": {               ← 🟢 Green (Data)
    "canada": {              ← 🟡 Yellow (Region)
      "meals": { ... }       ← 🟠 Orange (Category)
      "incidentals": { ... } ← 🟠 Orange (Category)
    }
  },
  "rateRules": { ... }       ← 🔴 Red (Rules)
}
```

## 📈 Data Hierarchy

```
Level 0: Database File
        │
Level 1: ├── metadata
        ├── regions / cities
        └── defaults / rules
        │
Level 2: └── canada / toronto
                │
Level 3:        ├── meals / rates
                └── incidentals / notes
                │
Level 4:        └── breakfast / standardRate
                        │
Level 5:                └── rate100 / value: 29.05
```

---

**Visual Guide Version:** 1.0  
**Created:** October 30, 2025  
**Purpose:** Quick reference for database structure and flow