Balancing Simplicity and Auditability: Database Models for User Funds

Managing user balances is an important part of many applications and systems. Here are some reasons why it's important:

Ensuring Proper Application Functioning

Applications dealing with users' financial data must guarantee accuracy and reliability of balance operations. This is especially crucial for financial apps, marketplaces, and gaming platforms. Developers and support staff need confidence that user balances are accounted for and changed properly according to users' actions.

Customer Loyalty

Users expect convenience and transparency in managing their funds. Effective balance management provides reliable tools for financial control. This increases trust in the product or service. Errors in balance management can lead to user dissatisfaction. Loss of funds or incorrect transactions can affect a company's reputation.

Fraud Prevention

Effective balance management also plays a role in preventing fraud. Developers and support should be able to quickly identify and investigate suspicious transactions and balance anomalies.

Ease of Support and Development

A well-designed balance management system makes life easier for developers and support staff. It allows quickly finding and fixing problems and providing swift support to users.

Compliance with Legal Requirements

In some industries, compliance with legal requirements and regulations is mandatory. Effective balance management helps adhere to standards and regulations.

Different Ways to Store and Work with Balances

1. Simple Balance Table

The first and easiest approach is to use a simple balance table. Each user is assigned a unique ID, and the user's current balance is stored as a number or other value.

To change the balance, you need to execute the following query (PostgreSQL syntax below):

UPDATE "Balances"
SET amount = amount + 100
WHERE "userId" = '5babf7f8';

ADVANTAGES

• Simplicity of Use

  This method is the easiest to implement and understand. Creating, updating, and querying the balance is done quickly and easily.

• Low Database Load

  Since only the current balance is stored, database queries can be more performant, which is important for scalable applications.

DISADVANTAGES

• No Transaction History

  The main disadvantage of this method is the lack of transaction history. This makes it unsuitable for situations where tracking and analysis are required.

• Low Trust in Balance

  Due to the lack of history, support and developers cannot be sure about the accuracy of the balance, especially if there were errors or incidents.

WHERE IT’S USED

• Online Stores

  A simple balance table can work for online stores where users have a balance to pay for orders. Simplicity of use and fast access to current balance are important factors.

• Gaming Apps

  In gaming apps, players may have internal balances to purchase in-game items. A simple balance table will provide easy management of player balances.

2. Transaction Table

The second approach involves using a transaction table to store the history of all operations.

In this table, every balance change operation is recorded indicating the user, amount, and timestamp. Each row represents a new account transaction for a specific user. If needed, fields explaining the reason for each record or other metadata can be added.

To get the current balance of each user, you need to make the following query:

SELECT "userId", SUM(amount) AS balance
FROM "Transaction"
GROUP BY "userId";

The query can be saved as a view.

ADVANTAGES

• Transaction History

  The main advantage of this method is the ability to track the history of all financial transactions. It provides transparency and allows for auditing.

• Granularity

  The high level of detail of the transactions allows analysing how the user came to the current balance. This can be useful for identifying errors and disputes.

DISADVANTAGES

• More Complex Queries

  Database queries to obtain the current balance may be more complex and slower than with a simple balance table. This may require more computing resources.

• Increased Data Volume

  Storing the transaction history can lead to increased data volume in the database. This may require additional storage.

• Support Complexity

  Suppose one user can transfer funds to another user. The records will look like this:

The change records for both balances are not linked in any way. Even if a linkage is made via a third field, there is no guarantee of transaction integrity. With potential manual edits, it will be difficult to navigate and maintain consistency.

WHERE IT’S USED

• Startups and Small Business

  The transaction table allows keeping a full history of client transactions and complying with legal requirements.

3. Transaction Table with Sender and Receiver

The third approach is to use a transaction table indicating the sender and receiver for each operation. This provides full transparency and cohesion of transactions between users.

Each row signifies a transfer from the "from" sender to the "to" receiver. The query to output all balances will look like this:

SELECT "userId", SUM(amount) AS balance
FROM (
  SELECT "to" AS "userId", amount FROM "Transactions"
  UNION
  SELECT "from", -amount FROM "Transactions"
) "T"
GROUP BY "userId";

For convenience, a view can be created from the query. For this method to work properly, service accounts need to be created; in this case, source-1 and source-2. These could be, for example, different ways to top up your own accounts. It may be important for users to know where the funds came from.

In addition, transfers between accounts take up only one record, and income and expenses are inextricably linked. When deleting, balances change immediately on both accounts.

ADVANTAGES

• Full Transaction Cohesion

  This method provides full cohesion between the sender's and recipient's transactions, allowing easy management of transfers and maintaining data integrity.

• Transparency of Fund Sources

  Specifying sender and recipient allows you to know exactly where the funds came from and where they went. This is important for financial institutions and investment accounting systems.

DISADVANTAGES

• Complex Queries

  Queries to get the current balance can be more complex and require more advanced SQL skills.

• Additional Work When Making Changes

  To work properly and conveniently, service accounts need to be created which will require building a directory.

WHERE IT’S USED

• Payment Systems

  Payment systems and banks can use this method to process transfers between different accounts and clients. Specifying sender and recipient simplifies tracking and accounting for transactions.

• Investment Accounting Systems

  In investment accounting systems, it is important to know where funds came from and where they went. A table indicating the sender and recipient allows tracking money flows and investment transactions.

• Blockchain

  In this field, transaction security and transparency are a priority.

In summary

The choice of method for working with balances in the database depends on the specific requirements of the system. A simple balance table can be an ideal solution for small projects that require minimizing complexity.

A transaction table is suitable for situations where it is important to have a history of transactions. A transaction table indicating the sender and recipient is the most powerful and flexible solution for complex financial systems. Each method has its advantages and disadvantages, and the choice should be based on the needs of a particular project.