php - user balance and income expenditure mysql table design and implementation plan

Question

This is my first time doing related business, so I don’t have enough experience, so I would like to learn some experience.
1. In addition to user id and balance, what other fields should the user balance table have?
2. Should the income statement and expense statement be separated or combined?
3. For example, register and get 100 coupons. After successful registration, add 100 to the balance field and add a record to the detailed table. Put these two steps in one transaction, or use other solutions such as message queue processing for the detailed table.

Answer 1

1. User ID, balance, start time (creation time), last transaction (income, expenditure) change time, total income, total expenditure (depend on the situation to decide whether it is redundant, redundancy is to avoid checking the database every time statistics are collected)
2. Look at the income statement and expenditure statement. If there are multiple sources of income and expenditure methods, it is suitable to separate them, such as income (1. Tips, 2. Recharge, 3. Transfer), expenditure (1. Purchase of business, 2. Recharge membership, 3. Transfer), etc.
3. Put it in Direct transactions can also be done at the same time, and message queues can also be used. As long as money is involved, just keep a good log. Because sending money and detailing operations are not time-consuming operations.
4. When calculating money, try to avoid floating point numbers. For example, 1 yuan should be saved as 100 points