0

I'm struggling to design my database. My database involves financial transactions of many kinds that are going to be used to calculate users' balances. Different transactions are different in structure and at the moment I'm storing them in different tables. When it comes to calculating user's balance I realized it was a big mess as I needed to select many different non-consistent rows from multiple tables and add them all up. So I thought what if I create a single table that would summarize the transaction (for example amount and user id who owns it) and reference a more detailed record and have a field to specify what kind of transaction it is. As it turned out: the thing that I was trying to implement is called 'polymorphic associations' and there's no way to implement it with MySQL in a reliable way. There are some workarounds but none of them guarantee the data integrity on the level I want it to be. In the end, I got so desperate that I started looking at other DBMSs but none of them seem to fit my case as well.

Do you have any recommendations? Perhaps, should I not be afraid of writing large and growing functions for calculating users' balances? Or should I go with the best option of modelling polymorphic associations in MySQL and leave a slight risk of data inconsistency? Or maybe there are DBMS that can solve my problem without workarounds?

The structure I have now The structure I have now Some transactions are linked directly with the user, while others through another table. My aim is to calculate the sum of all the transactions for a single user, and it would be a really big and growing SQL statement to get all the rows for every single kind of transaction.

This is how I want it to be structured This is how I want it to be structured

That is I want to have a single table for easier calculations, but the table has to enforce that the record it presents exists.

nbk
  • 45,398
  • 8
  • 30
  • 47
zabbir
  • 59
  • 5
  • It would be very helpful if you would provide a simple diagram of your tables and their relation to one another. – STLDev Oct 26 '19 at 00:06
  • Please [use text, not images/links, for text--including tables & ERDs.](https://meta.stackoverflow.com/q/285551/3404097) Paraphrase or quote from other text. Give just what you need & relate it to your problem. Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. Include a legend/key & explanation with an image. – philipxy Oct 26 '19 at 07:12
  • Possible duplicate of [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Oct 26 '19 at 07:14
  • This is a faq. Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags, & read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. – philipxy Oct 26 '19 at 07:15

1 Answers1

1
BEGIN;
SELECT balance FROM Balances WHERE user_id = 123 FOR UPDATE;
do stuff in other table(s)
UPDATE Balances SET balance = balance + 876 WHERE user_id = 123
COMMIT;

Since your actions are scattered among various tables, you would need various copies of that code.

Note that the user's balance is only in one place in one table

The FOR UPDATE is critical for proper locking.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • I don’t want to store users balance as an integer. I was told it’s a bad idea and the balance must always be calculated from a history. – zabbir Oct 26 '19 at 01:05
  • The Auditor will go through the history to make sure you added correctly. The "876" would be some `DECIMAL` value that comes from wherever money comes from, not a hard-coded integer. (The 123 also comes from outside.) – Rick James Oct 26 '19 at 01:09