0

I am trying to work on a personal expense management.

I do have budgets, loans, lending, savings and salary management. I am kinda confused between choosing between approaches for tracking transaction details for all these different types.

I have separate tables these types cause they have different values and data set to be captured.

All these transaction types require transactions.

CREATE TABLE Budget (
BudgetId INT NOT NULL AUTO_INCREMENT,
TargetAmount FLOAT NOT NULL,
Description VARCHAR(150) NULL DEFAULT '',
StartDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
EndDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
CreatedAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
UpdatedAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UserId INT NOT NULL,
CONSTRAINT `FK_Budget_User` FOREIGN KEY (UserId)
    REFERENCES User (UserId)
    ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY (BudgetId)
);

CREATE TABLE Loan (
LoanId INT NOT NULL AUTO_INCREMENT,
`Name` VARCHAR(150) NOT NULL,
`Description` VARCHAR(255),    
Tenure INT NOT NULL,
InterestAmount FLOAT NOT NULL,
Emi FLOAT NOT NULL,
EmiDate DATETIME,
BankName VARCHAR(100) NOT NULL,    
StartDate DATETIME NOT NULL,
EndDate DATETIME NOT NULL,
CreatedAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
UpdatedAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UserId INT NOT NULL,
CONSTRAINT `FK_Loan_User` FOREIGN KEY (UserId)
    REFERENCES User (UserId) ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY (LoanId)
);

CREATE TABLE Savings (
SavingsId INT NOT NULL AUTO_INCREMENT,
Amount FLOAT NOT NULL,
CreatedAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
UpdatedAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UserId INT NOT NULL,
CONSTRAINT `FK_Savings_User` FOREIGN KEY (UserId)
    REFERENCES User (UserId) ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY (SavingsId)
);

Method 1

I have one generic table where I hold the TypeId which will specify the type like Budget | loan | savings and then I capture the primary index for those type as TypeReferenceId.

If the type is Budget the TypeReferenceId would refer to BudgetId. The problem here is transaction and budget has no relation ship and this is kinda bad to maintain this in an ORM as well.

I can create a transaction without creating a budget or any type. which should not be allowed.

CREATE TABLE `Transaction` (
TransactionId INT NOT NULL AUTO_INCREMENT,
Amount FLOAT NOT NULL,
`Description` VARCHAR(150) NOT NULL,
`Date` DATETIME NOT NULL,
UserId INT NOT NULL,
CategoryTransactionId INT NOT NULL,
PaymentMethodId INT NOT NULL,
RecurringType ENUM('weekly', 'fortnight', 'monthly', 'yearly', 'none') DEFAULT 'none',
`Type` ENUM('savings', 'loan', 'budget', 'lending', '') DEFAULT '',
TypeId INT NOT NULL DEFAULT 0,
Credit BOOLEAN NOT NULL DEFAULT FALSE,
CreatedAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
UpdatedAt DATETIME NOT NULL ON UPDATE CURRENT_TIMESTAMP,
CONSTRAINT `FK_Transaction_User` FOREIGN KEY (UserId)
    REFERENCES User (UserId)
    ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_Transaction_CategoryTransaction` FOREIGN KEY (CategoryTransactionId)
    REFERENCES CategoryTransaction (CategoryTransactionId)
    ON UPDATE CASCADE,
CONSTRAINT `FK_Transaction_PaymentMethod` FOREIGN KEY (PaymentMethodId)
    REFERENCES PaymentMethod (PaymentMethodId)
    ON UPDATE CASCADE,
PRIMARY KEY (TransactionId)
);

Method 2

Would be to add 5 new columns in transaction table to maintain relationship between the transaction

CREATE TABLE `Transaction` (
TransactionId INT NOT NULL AUTO_INCREMENT,
Amount FLOAT NOT NULL,
`Description` VARCHAR(150) NOT NULL,
`Date` DATETIME NOT NULL,
UserId INT NOT NULL,
CategoryTransactionId INT NULL,
PaymentMethodId INT NOT NULL,
RecurringTypeId INT NOT NULL,
TransactionTypeId INT NOT NULL,
BudgetId INT NULL,
LoanId INT NULL,
LendingId INT NULL,
PayslipId INT NULL,
SavingsId INT NULL,
Credit BOOLEAN NOT NULL DEFAULT FALSE,
CreatedAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
UpdatedAt DATETIME NOT NULL ON UPDATE CURRENT_TIMESTAMP,
CONSTRAINT `FK_Transaction_User` FOREIGN KEY (UserId)
    REFERENCES User (UserId)
    ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_Transaction_CategoryTransaction` FOREIGN KEY (CategoryTransactionId)
    REFERENCES CategoryTransaction (CategoryTransactionId)
    ON UPDATE CASCADE,
CONSTRAINT `FK_Transaction_RecurringType` FOREIGN KEY (RecurringTypeId)
    REFERENCES RecurringType (RecurringTypeId)
    ON UPDATE CASCADE,
CONSTRAINT `FK_Transaction_PaymentMethod` FOREIGN KEY (PaymentMethodId)
    REFERENCES PaymentMethod (PaymentMethodId)
    ON UPDATE CASCADE,
CONSTRAINT `FK_Transaction_Budget` FOREIGN KEY (BudgetId)
    REFERENCES Budget (BudgetId)
    ON UPDATE CASCADE,
CONSTRAINT `FK_Transaction_Loan` FOREIGN KEY (LoanId)
    REFERENCES Loan (LoanId)
    ON UPDATE CASCADE,
CONSTRAINT `FK_Transaction_Lending` FOREIGN KEY (LendingId)
    REFERENCES Lending (LendingId)
    ON UPDATE CASCADE,
CONSTRAINT `FK_Transaction_Payslip` FOREIGN KEY (PayslipId)
    REFERENCES Payslip (PayslipId)
    ON UPDATE CASCADE,
CONSTRAINT `FK_Transaction_Savings` FOREIGN KEY (SavingsId)
    REFERENCES Savings (SavingsId)
    ON UPDATE CASCADE,
PRIMARY KEY (TransactionId)
);

This maintains the relation ship but maintaining the columns inside the transaction would be hard to scale the application in future.

Method 3

Would be to create different tables for each type

  • Budget Transaction
  • Lending Transaction
  • Savings Transaction
  • Loan Transaction
  • Salary Transaction

but how complex would be for querying all the transactions for a user and complex reports ?

Is there any other way of handle this efficiently ?

Any help is appreciated, Thanks in advance

  • Sounds like you have too much complexity. A transaction should include taking money out of one table and putting it into table, and perhaps adjusting the 'budget' table. Leave out the FKs until you get your app code figured out. They only complicate the picture. – Rick James Sep 9 at 20:32
  • Check out this Google of Open Source personal finance software. You can check out their schemas and see if you can adapt them to your needs. – Vérace Sep 9 at 21:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.