DESIGN.md

Design Document: Personal Finance Management System

By: Yurii

Purpose:

Create a database to manage personal finances, including tracking income and expenses, managing budgets, monitoring account balances, and generating financial reports.

Scope

Included:

• Users
• Accounts
• Transaction Categories
• Transactions

Excluded:

• Integration with external APIs
• Automatic transaction classification

Functional Requirements

Users should be able to::

• Register and log in
• Manage accounts and categories
• Manage transactions
• View balances and reports
• Set and track budgets

Users should not be able to:

• Integrate with banking APIs
• Automatically classify transactions

Entities and Relationships

Users

• Attributes:
  • UserID: INT, Primary Key, Auto Increment
  • FirstName: VARCHAR(255), Not Null
  • LastName: VARCHAR(255), Not Null
  • Email: VARCHAR(255), Unique, Not Null
  • Password: VARCHAR(255), Not Null

Accounts

• Attributes:
  • AccountID: INT, Primary Key, Auto Increment
  • UserID: INT, Foreign Key (references Users(UserID)), Not Null
  • AccountName: VARCHAR(255), Not Null
  • Balance: DECIMAL(10, 2), Not Null

Categories

• Attributes:
  • CategoryID: INT, Primary Key, Auto Increment
  • UserID: INT, Foreign Key (references Users(UserID)), Not Null
  • CategoryName: VARCHAR(255), Not Null

Transactions

• Attributes:
  • TransactionID: INT, Primary Key, Auto Increment
  • UserID: INT, Foreign Key (references Users(UserID)), Not Null
  • AccountID: INT, Foreign Key (references Accounts(AccountID)), Not Null
  • CategoryID: INT, Foreign Key (references Categories(CategoryID))
  • Amount: DECIMAL(10, 2), Not Null
  • Date: DATE, Not Null
  • Description: VARCHAR(255)

Relationships

• Users - Accounts: One-to-Many (One user can have multiple accounts)
• Users - Categories: One-to-Many (One user can create multiple categories)
• Users - Transactions: One-to-Many (One user can have multiple transactions)
• Accounts - Transactions: One-to-Many (One account can have multiple transactions)
• Categories - Transactions: One-to-Many (One category can be associated with multiple transactions)

Optimizations

• Indexes on UserID, AccountID, CategoryID for faster queries
• Views for income and expense reports

Limitations

• No integration with external APIs
• Limited analytics capabilities

ER Diagram

erDiagram
    USERS {
        INT UserID PK "Auto Increment"
        VARCHAR FirstName "255 Not Null"
        VARCHAR LastName "255 Not Null"
        VARCHAR Email "255 Unique Not Null"
        VARCHAR Password "255 Not Null"
    }
    ACCOUNTS {
        INT AccountID PK "Auto Increment"
        INT UserID FK "references Users(UserID) Not Null"
        VARCHAR AccountName "255 Not Null"
        DECIMAL Balance "10,2 Not Null"
    }
    CATEGORIES {
        INT CategoryID PK "Auto Increment"
        INT UserID FK "references Users(UserID) Not Null"
        VARCHAR CategoryName "255 Not Null"
    }
    TRANSACTIONS {
        INT TransactionID PK "Auto Increment"
        INT UserID FK "references Users(UserID) Not Null"
        INT AccountID FK "references Accounts(AccountID) Not Null"
        INT CategoryID FK "references Categories(CategoryID)"
        DECIMAL Amount "10,2 Not Null"
        DATE Date "Not Null"
        VARCHAR Description "255"
    }

    USERS ||--o{ ACCOUNTS: "has"
    USERS ||--o{ CATEGORIES: "creates"
    USERS ||--o{ TRANSACTIONS: "makes"
    ACCOUNTS ||--o{ TRANSACTIONS: "includes"
    CATEGORIES ||--o{ TRANSACTIONS: "associated with"
    

Loading

queries.sql

-- View for income and expense reports
CREATE VIEW IncomeExpenseReport AS
SELECT 
    u.UserID, 
    u.FirstName, 
    u.LastName, 
    a.AccountName, 
    t.CategoryID, 
    t.Amount, 
    t.Date, 
    t.Description
FROM 
    Transactions t
JOIN 
    Users u ON t.UserID = u.UserID
JOIN 
    Accounts a ON t.AccountID = a.AccountID
ORDER BY 
    t.Date;

-- Query to get the balance of all accounts for a user
SELECT 
    u.UserID, 
    u.FirstName, 
    u.LastName, 
    SUM(a.Balance) AS TotalBalance
FROM 
    Users u
JOIN 
    Accounts a ON u.UserID = a.UserID
GROUP BY 
    u.UserID, u.FirstName, u.LastName;

-- Query to get transactions for a specific account
SELECT 
    t.TransactionID, 
    t.Amount, 
    t.Date, 
    t.Description, 
    c.CategoryName
FROM 
    Transactions t
JOIN 
    Categories c ON t.CategoryID = c.CategoryID
WHERE 
    t.AccountID = 1;
    

schema.sql

-- Schema for Users Table
CREATE TABLE Users (
    UserID INT PRIMARY KEY AUTO_INCREMENT,
    FirstName VARCHAR(255) NOT NULL,
    LastName VARCHAR(255) NOT NULL,
    Email VARCHAR(255) UNIQUE NOT NULL,
    Password VARCHAR(255) NOT NULL
);

-- Schema for Accounts Table
CREATE TABLE Accounts (
    AccountID INT PRIMARY KEY AUTO_INCREMENT,
    UserID INT NOT NULL,
    AccountName VARCHAR(255) NOT NULL,
    Balance DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (UserID) REFERENCES Users(UserID)
);

-- Schema for Categories Table
CREATE TABLE Categories (
    CategoryID INT PRIMARY KEY AUTO_INCREMENT,
    UserID INT NOT NULL,
    CategoryName VARCHAR(255) NOT NULL,
    FOREIGN KEY (UserID) REFERENCES Users(UserID)
);

-- Schema for Transactions Table
CREATE TABLE Transactions (
    TransactionID INT PRIMARY KEY AUTO_INCREMENT,
    UserID INT NOT NULL,
    AccountID INT NOT NULL,
    CategoryID INT,
    Amount DECIMAL(10,2) NOT NULL,
    Date DATE NOT NULL,
    Description VARCHAR(255),
    FOREIGN KEY (UserID) REFERENCES Users(UserID),
    FOREIGN KEY (AccountID) REFERENCES Accounts(AccountID),
    FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
);

-- Create indexes
CREATE INDEX idx_user_email ON Users(Email);
CREATE INDEX idx_account_user ON Accounts(UserID);
CREATE INDEX idx_category_user ON Categories(UserID);
CREATE INDEX idx_transaction_user ON Transactions(UserID);
CREATE INDEX idx_transaction_account ON Transactions(AccountID);
CREATE INDEX idx_transaction_category ON Transactions(CategoryID);

-- Sample Data Insertion

-- Inserting sample data into Users
INSERT INTO Users (FirstName, LastName, Email, Password) VALUES ('John', 'Doe', 'john.doe@example.com', 'password123');
INSERT INTO Users (FirstName, LastName, Email, Password) VALUES ('Jane', 'Smith', 'jane.smith@example.com', 'password123');

-- Inserting sample data into Accounts
INSERT INTO Accounts (UserID, AccountName, Balance) VALUES (1, 'Checking', 1000.00);
INSERT INTO Accounts (UserID, AccountName, Balance) VALUES (2, 'Savings', 5000.00);

-- Inserting sample data into Categories
INSERT INTO Categories (UserID, CategoryName) VALUES (1, 'Groceries');
INSERT INTO Categories (UserID, CategoryName) VALUES (2, 'Utilities');

-- Inserting sample data into Transactions
INSERT INTO Transactions (UserID, AccountID, CategoryID, Amount, Date, Description) 
VALUES (1, 1, 1, 100.00, '2024-06-01', 'Grocery shopping');
INSERT INTO Transactions (UserID, AccountID, CategoryID, Amount, Date, Description) 
VALUES (2, 2, 2, 150.00, '2024-06-01', 'Utility bill');

my exercise week 3: