Hello, I'M struggling to design my database. There are many types

Question

Hello, I'M struggling to design my database. There are many types

of financial transactions and each is stored in its own table. 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. At 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 DBSMs that can solve my problem without workarounds?

0

25.10.2019

3 ответов

13 просмотров

Fayaz Khan

What's the inconsistency problem that you're running into?

0

26.10.2019

Ludovic 'Archivist' Lagouardette

Polymorphism in RDBMS is mostly unimplemented in most major databases as I think it is currently not supported by the SQL language. This is the kind of thing that may either require either a good amount of voluntary bad design, a document based database with correct acid semantics or a domain specific DBMS

0

26.10.2019

Ibrahim Ngeno · Accepted Answer

Ibrahim Ngeno

postgreSQL?

0

26.10.2019

Похожие чаты

Hello, I'M struggling to design my database. There are many types

3 ответов

Похожие вопросы