title: Table structure for financial transactions in Mysql?
body: <p>I am developing a system that will have financial transactions. In this system the user can insert or withdraw money, buy and sell products, send money to other users, etc.</p>
  
  <p>I currently have a user table that, in addition to the registration data, also contains the total balance of money that the user has in his <strong>wallet</strong>.</p>
  
  <p>And another table of transactions, which indicates all transactions that were made, money that entered or left the account (<strong>in_or_out</strong>), which reference of the transaction (<strong>trans_ref</strong>) and <strong>value</strong> that was transferred.</p>
  
  <p>Below is the current structure:</p>
  
  <pre><code>|        Table USERS       |
  ----------------------------
  |cod        varchar (index)|
  |full_name  varchar        |
  |username   varchar        |
  |password   varchar        |
  |wallet     decimal        |
  |created_at timestamp      |
  |activate   int            |
  
  
  |    Table TRANSACTIONS    |
  ----------------------------
  |cod        varchar (index)|
  |user_cod   varchar        |
  |trans_ref  varchar        |
  |in_or_out  int            |
  |value      decimal        |
  |created_at timestamp      |
  |status     int            |
  </code></pre>
  
  <p>My fear is that due to an error in the transaction, the amount will not be added to the user table. Or that the user makes a payment and the amount is not deducted from the table. Because I will always be taking into account the value of the user's * wallet * as the total money in he account.</p>
  
  <p>So if he makes a purchase for example of $1 thousand dollars, and this amount is not deducted from his account, he will still have the same amount to spend, which can give a loss later for the company, since with 1 million users, it would be difficult to know which user gave the problem.</p>
  
  <p>I thought, instead of making a total of the wallet in the user table, that I make a query to take the sum of the transactions. Then in this case, if I have 1 million users, and 1 billion transactions, it would be too heavy to get data of the sum up all the time, which can bring a lot of slowness in system queries.</p>
  
  <p>Could someone who has some experience with calculations, or similar systems, guide me with a better logic to structure these tables?</p>
  
  <p>Remembering that the current structure can still be modified in any necessary way. </p>