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. userId balance 59a20c57 300 5babf7f8 100 d071d371 200 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. id userId amount createdAt 1 5babf7f8 100 2022-10-25 2 5babf7f8 50 2022-10-26 3 d071d371 300 2022-10-27 4 5babf7f8 -100 2022-10-28 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"; userId balance 5babf7f8 50 d071d371 300 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: id userId amount createdAt 5 d071d371 -200 2022-10-29 6 5babf7f8 200 2022-10-29 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. id from to amount createdAt 1 source-1 d071d371 500 2022-10-24 2 source-2 5babf7f8 400 2022-10-25 3 source-2 d071d371 100 2022-10-26 4 d071d371 source-2 100 2022-10-27 5 d071d371 5babf7f8 200 2022-10-28 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.
