I am working on a simple web app that requires financial transactions to be stored and invoked and used for each user. My question is how thatdata should be stored. Should each user have their own database or would it be fine to have every single users’ data stored in one table? Obviously different types of data will have separate tables. If they are stored together would it be fine if each row had an username entry in order to pull a users individual data?
Having the correct indexes should take care of the performance issue.
It really depends on what type of financial transactions we’re talking about. It also depends on the amount of data you’re going to store. Let’s say that I’m a bank and I have a few thousands of customers. It also depends on the database. Logically, I would have a table for users with all the information related to them, and a table for transactions (pay-ins, withdrawls, etc) and a third table for bank accounts (bank account nbr, type of account, etc).
The users table needs to be related at least to bank accounts (using user ID to create the relationship, more than the name), and bank accounts related to the transactions table. Then I could query the transactions of a paritcular user by querying the three tables. This is a logical structure.
Now, would it be better to create a transaction table for each user? That would change the logic and for sure the whole structure of the database. It will be certainly harder to maintain yet searches for the transactions of a particular user would be faster as it would not be necessary to use a huge table for it, we will be using a table that was created only for this user.
To know if it is worth it or not, we would need to benchmark and compare results. That would mean create the database structures, fill them with dummy data, and do the queries that we would normaly do and use the database optimization and measuring tools to know what’s quicker.