ERD design (AI generated) - Project-MRP/finance-tracker GitHub Wiki

Here's how we can design an Entity-Relationship Diagram (ERD) and a corresponding schema for your expense manager app. Iā€Ø'll outline the entities, their relationships, and fields, followed by the ERD.

Entities and Relationships

User, Account, Transaction, Category

  1. User

    • Fields:
      • user_id (Primary Key)
      • username
      • email
      • password_hash (if EMAIL based login)
      • created_at
      • updated_at
    • Relationship: One User can have many Accounts and many Transactions.

  2. Account

    • Fields:
      • account_id (Primary Key)
      • user_id (Foreign Key)
      • account_name
      • account_type (e.g., Savings, Checking, Credit Card)
      • balance
      • currency
      • created_at
      • updated_at
    • Relationship: One Account can have many Transactions. Each Account belongs to one User.

  3. Transaction

    • Fields:
      • transaction_id (Primary Key)
      • user_id (Foreign Key)
      • account_id (Foreign Key)
      • category_id (Foreign Key)
      • transaction_type_id/transaction_type (Foreign Key) (ENUM can be used here)
      • amount
      • note [Default value: Generated based on the tag]
      • description
      • image_url
      • transaction_date
      • created_at
      • updated_at
    • Relationship: Each Transaction belongs to one Account, one Category, and one Transaction Type.

  4. Category

    • Fields:
      • category_id (Primary Key)
      • user_id (Foreign Key, optional for personalized categories)
      • category_name
      • category_type (e.g., Income, Expense)
      • created_at
      • updated_at
    • Relationship: One Category can have many Transactions. Categories can be user-specific or global.

  5. Transaction Type

    • Fields:
      • transaction_type_id (Primary Key)
      • type_name (e.g., Income, Expense, Transfer)
      • created_at
      • updated_at
    • Relationship: Each Transaction belongs to one Transaction Type.

Entity-Relationship Diagram (ERD)

I'll describe the ERD structure, and you can visualize or create it using a tool:

  • User (1) ā†ā†’ (M) Account
  • User (1) ā†ā†’ (M) Transaction
  • Account (1) ā†ā†’ (M) Transaction
  • Transaction (M) ā†ā†’ (1) Category
  • Transaction (M) ā†ā†’ (1) Transaction Type

Schema Definition

-- Users Table
CREATE TABLE Users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Accounts Table
CREATE TABLE Accounts (
    account_id SERIAL PRIMARY KEY,
    user_id INT REFERENCES Users(user_id),
    account_name VARCHAR(50) NOT NULL,
    account_type VARCHAR(50),
    balance DECIMAL(15, 2) DEFAULT 0.00,
    currency VARCHAR(10) DEFAULT 'USD',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Categories Table
CREATE TABLE Categories (
    category_id SERIAL PRIMARY KEY,
    user_id INT REFERENCES Users(user_id),
    category_name VARCHAR(50) NOT NULL,
    category_type VARCHAR(50) CHECK (category_type IN ('Income', 'Expense')),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Transaction Types Table
CREATE TABLE Transaction_Types (
    transaction_type_id SERIAL PRIMARY KEY,
    type_name VARCHAR(50) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Transactions Table
CREATE TABLE Transactions (
    transaction_id SERIAL PRIMARY KEY,
    user_id INT REFERENCES Users(user_id),
    account_id INT REFERENCES Accounts(account_id),
    category_id INT REFERENCES Categories(category_id),
    transaction_type_id INT REFERENCES Transaction_Types(transaction_type_id),
    amount DECIMAL(15, 2) NOT NULL,
    description TEXT,
    transaction_date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Summary of the Structure

  • Users manage their Accounts and perform Transactions.
  • Transactions are categorized under different Categories (like Food, Salary) and associated with a specific Transaction Type (like Income or Expense).
  • Each Account holds multiple transactions and belongs to a single user.
  • Categories can be global or specific to a user, allowing personalized financial tracking.

This schema should give you a solid foundation for building your expense manager app, with clear relationships and data integrity.