Exercise 5: Personal Finance

erDiagram

    account {
        string name
        decimal currency
        int key PK "id_account"
    }

    category {
        string name
        string label
        string uuid
        int key PK "id_category"
    }

    account_journal {
        decimal value
        datetime date
        int key PK "id_account_journal"
        int id_account FK "id_account"
        int id_category FK "id_category"
    }

    account one--many account_journal : has
    category one--many account_journal : has

Description #

• Manage personal finance by keeping track of accounts, categories, and journal entries for transactions.

• Categories represent transaction types (“Withdrawal”/“Deposit”).

• Each journal entry records the transaction value, date, account, and category.

• An account can have multiple account_journals. More account journals can have the same category.

• In the entity “category”, name represents the name of the category that will be displayed on the screen, label represents the value that programmers will use to filter the categories, and the uuid represents a value to uniquely distinguish the categories.

• Note: The value of the field “uuid” is generated automatically.

Tasks #

• account/category/account_journal: Add, Edit, Delete.

  • The field “Category” from entity “account_journal” will be read-only in edit view.

• Create a catalog for entities: “account”, “category”.

• Deposit: add a new record in “account_journal” with the deposited value and date (entity action).

• Withdrawal: add a new record in “account_journal” with the withdrawn value and date (entity action).

• Create event listeners for tables that needs validations (e.g. adding an account without a name, a type, etc., should not be allowed).

• Account statement (interval):

  • Extract account statement for a specific interval for each account.

• Account balance (at a date):

  • Extract balance for a specific interval for each account.

• Balance by categories (interval):

  • Extract balance for a specific interval for each category.

• Total deposits and withdrawals for each account in a specific interval.