2024-10-17|閱讀時間 ‧ 約 0 分鐘

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 中進行相關的實作!


分享至
成為作者繼續創作的動力吧!
© 2024 vocus All rights reserved.