D8 - 深入設計個人財務管理系統的資料庫結構

哈囉，各位！我們已經搭建好了開發環境，也處理好了版本控制。現在，是時候來設計我們的資料庫了。這一步可不能馬虎，因為資料庫就像是系統的心臟，設計得好，才能讓整個系統順暢運作。

一、需求分析

在開始之前，讓我們重新整理一下我們的系統需求：

1. 使用者管理：使用者可以註冊、登入，並管理自己的帳戶。
2. 銀行帳戶管理：使用者可以新增、編輯、刪除銀行帳戶。
3. 財務紀錄管理：記錄每個銀行帳戶的收入與支出。
4. 分類管理：對財務紀錄進行分類，如「飲食」、「交通」、「薪資」等。
5. 報表功能：生成收支報表，讓使用者了解自己的財務狀況。

二、資料庫設計概述

根據需求，我們需要設計以下資料表：

1. users：使用者表
2. bank_accounts：銀行帳戶表
3. categories：分類表
4. transactions：財務紀錄表
   接下來，我們將逐一詳細介紹每個資料表的設計，並提供實際的 MySQL 建表語法。

三、資料表詳細設計

1. users 表—使用者資訊管理

用途：儲存使用者的基本資訊，處理登入、註冊和驗證。

欄位設計：

• id (INT UNSIGNED AUTO_INCREMENT PRIMARY KEY)：使用者唯一識別碼。
• username (VARCHAR(50) NOT NULL UNIQUE)：使用者名稱。
• email (VARCHAR(100) NOT NULL UNIQUE)：電子郵件。
• password (VARCHAR(255) NOT NULL)：加密後的密碼。
• created_at (TIMESTAMP DEFAULT CURRENT_TIMESTAMP)：帳號建立時間。
• updated_at (TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)：帳號最後更新時間。

建表語法：

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

設計原因：

• 安全性：密碼必須經過加密後儲存，不能以明文形式保存。
• 唯一性：username 和 email 設為唯一，防止重複註冊。
• 時間戳記：方便追蹤使用者的註冊和更新時間。

2. bank_accounts 表—銀行帳戶管理

用途：管理使用者的銀行帳戶。

欄位設計：

• id (INT UNSIGNED AUTO_INCREMENT PRIMARY KEY)：帳戶唯一識別碼。
• user_id (INT UNSIGNED NOT NULL)：關聯到 users 表的使用者 ID。
• account_name (VARCHAR(100) NOT NULL)：帳戶名稱，例如「薪資帳戶」。
• account_number (VARCHAR(50))：銀行帳號（可選）。
• bank_name (VARCHAR(100))：銀行名稱（可選）。
• balance (DECIMAL(15,2) NOT NULL DEFAULT 0.00)：帳戶餘額。
• created_at (TIMESTAMP DEFAULT CURRENT_TIMESTAMP)：帳戶建立時間。
• updated_at (TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)：帳戶最後更新時間。

建表語法：

CREATE TABLE bank_accounts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    account_name VARCHAR(100) NOT NULL,
    account_number VARCHAR(50),
    bank_name VARCHAR(100),
    balance DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

設計原因：

• 多帳戶支持：一個使用者可能有多個銀行帳戶，需要進行區分。
• 關聯性：透過 user_id 與 users 表關聯，確保帳戶屬於正確的使用者。
• 資料完整性：ON DELETE CASCADE 確保當使用者被刪除時，相關的銀行帳戶也會被刪除。

3. categories 表—分類管理

用途：管理財務紀錄的分類。

欄位設計：

• id (INT UNSIGNED AUTO_INCREMENT PRIMARY KEY)：分類唯一識別碼。
• user_id (INT UNSIGNED NOT NULL)：關聯到 users 表的使用者 ID。
• category_name (VARCHAR(100) NOT NULL)：分類名稱。
• type (ENUM('income', 'expense') NOT NULL)：類型，收入或支出。
• created_at (TIMESTAMP DEFAULT CURRENT_TIMESTAMP)：分類建立時間。
• updated_at (TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)：分類最後更新時間。

建表語法：

CREATE TABLE categories (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    category_name VARCHAR(100) NOT NULL,
    type ENUM('income', 'expense') NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

設計原因：

• 個人化分類：每個使用者可以有自己的分類。
• 類型區分：type 欄位區分收入和支出，方便在查詢時進行過濾。
• 資料完整性：確保分類與使用者正確關聯。

4. transactions 表—財務紀錄管理

用途：記錄每一筆財務交易。

欄位設計：

• id (INT UNSIGNED AUTO_INCREMENT PRIMARY KEY)：交易唯一識別碼。
• user_id (INT UNSIGNED NOT NULL)：關聯到 users 表的使用者 ID。
• bank_account_id (INT UNSIGNED NOT NULL)：關聯到 bank_accounts 表的帳戶 ID。
• category_id (INT UNSIGNED NOT NULL)：關聯到 categories 表的分類 ID。
• type (ENUM('income', 'expense') NOT NULL)：交易類型。
• amount (DECIMAL(15,2) NOT NULL)：交易金額。
• transaction_date (DATE NOT NULL)：交易日期。
• description (VARCHAR(255))：備註（可選）。
• created_at (TIMESTAMP DEFAULT CURRENT_TIMESTAMP)：交易建立時間。
• updated_at (TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)：交易最後更新時間。

建表語法：

CREATE TABLE transactions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    bank_account_id INT UNSIGNED NOT NULL,
    category_id INT UNSIGNED NOT NULL,
    type ENUM('income', 'expense') NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    transaction_date DATE NOT NULL,
    description VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (bank_account_id) REFERENCES bank_accounts(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
);

設計原因：

• 資料完整性：確保每筆交易都與正確的使用者、帳戶、分類關聯。
• 類型冗餘：即使分類的類型被修改，交易的類型仍然保持不變。
• 金額精確度：使用 DECIMAL 型別，確保金額計算的精確性。
• 時間戳記：transaction_date 用於記錄實際交易發生的日期，而不是資料建立的時間。

四、資料表關聯關係

E-R 圖文字描述

以下是根據你提供的文字描述生成的完整 E-R 圖。這張 E-R 圖展示了 users、bank_accounts、categories 和 transactions 之間的關聯，並且清晰表示了每個實體之間的多對一或一對多的關係。

• users：
• • 一對多關聯至 bank_accounts。
  • 一對多關聯至 categories。
  • 一對多關聯至 transactions。
• bank_accounts：
• • 多對一關聯至 users。
  • 一對多關聯至 transactions。
• categories：
• • 多對一關聯至 users。
  • 一對多關聯至 transactions。
• transactions：
• • 多對一關聯至 users。
  • 多對一關聯至 bank_accounts。
  • 多對一關聯至 categories。

E-R 圖的 Mermaid 代碼

erDiagram
    users {
        string user_id PK
        string name
        string email
        string password
    }
    
    bank_accounts {
        string account_id PK
        string user_id FK
        string account_name
        string account_number
        float balance
    }
    
    categories {
        string category_id PK
        string user_id FK
        string category_name
    }
    
    transactions {
        string transaction_id PK
        string user_id FK
        string account_id FK
        string category_id FK
        float amount
        string date
        string description
    }

    users ||--o{ bank_accounts : owns
    users ||--o{ categories : defines
    users ||--o{ transactions : makes

    bank_accounts ||--o{ transactions : records

    categories ||--o{ transactions : categorizes

owns：表示一個使用者擁有多個銀行帳戶。

defines：表示一個使用者定義多個分類。makes：表示一個使用者創建多筆交易。records：表示銀行帳戶記錄交易。categorizes：表示分類標記交易。

說明

• users：
• • 和 bank_accounts、categories、transactions 有一對多的關聯，表示一個使用者可以擁有多個銀行帳戶、分類及交易紀錄。
• bank_accounts：
• • 多對一關聯到 users，表示一個銀行帳戶只屬於一個使用者。
  • 和 transactions 有一對多關聯，表示一個帳戶可以有多筆交易。
• categories：
• • 多對一關聯到 users，表示每個分類屬於一個使用者。
  • 和 transactions 有一對多關聯，表示一個分類可以對應到多筆交易。
• transactions：
• • 多對一關聯到 users、bank_accounts 和 categories，表示每筆交易都與一個使用者、一個銀行帳戶及一個分類相關聯。

五、實際應用場景舉例

假設一個使用者張三，使用我們的系統進行以下操作：

1. 註冊帳號：在 users 表中新增一筆資料。
2. 新增銀行帳戶：在 bank_accounts 表中新增「薪資帳戶」。
3. 新增分類：在 categories 表中新增「薪資」分類，類型為收入。
4. 記錄收入：在 transactions 表中新增一筆交易，金額為 50000，分類為「薪資」，關聯到「薪資帳戶」。
5. 查看報表：系統根據 transactions 表中的資料，生成當月的收入報表。

六、未來可能的擴充功能

• 多幣別支持：在相關表格中新增 currency 欄位，處理不同貨幣的交易。
• 共享帳戶：允許多個使用者共享同一個銀行帳戶，需要調整 bank_accounts 表的設計。
• 提醒功能：設定定期交易或預算提醒，可能需要新增 schedules 或 alerts 表。

我們詳細設計了個人財務管理系統的資料庫結構，涵蓋了使用者管理、銀行帳戶管理、財務紀錄管理和分類管理等核心功能。

良好的資料庫設計是系統開發的基石，它直接影響到系統的性能、擴充性和維護成本。透過清晰的關聯關係和資料完整性約束，我們能確保資料的一致性和可靠性，為後續的應用開發奠定了堅實的基礎。接下來，我們將在 Laravel 中實現這些資料表，並開發相應的後端 API，讓我們的系統從設計走向實際應用。這將使我們能夠更好地服務使用者，提供可靠的個人財務管理工具。

下一篇，我們將在 Laravel 中進行相關的實作！