I'm making a website which has to do with money transactions. Everything was okay untill I came across the dilemma
Should I have one table to store all transaction history or I should have a seperated table for every user?
I tried to logicaly think the best solution and this is what I thought:
One table for all
- Pretty much when every other dev would do
- However what if i have 100.000 transactions inside?
- That would slow down analytis for each user
- MariaDB would have to do much more work
One table for each user
- Every user is isolated
- MariaDB can query faster
- Easy data analysis
- However what if 10.000 users have a table?
- That would flood the database (not big deal if it can handle it)
I dont find anything wrong with using a table for each user but is it a common tachnique to design a database like that?
I found this post over here: What are the advantages/disadvantages of creating a new set of tables for each user?
which pretty much tells that is a terrible idea to have multiple table but does it really matter since in my case
- I will never change the structure
- I will never edit the data inside, only add new rows
- I will enhance the security (principle of least privilege)