One table for all transaction VS. Table for each user

Question

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

1. I will never change the structure
2. I will never edit the data inside, only add new rows
3. I will enhance the security (principle of least privilege)

Answer 1

Your arguments in favor table-per-user are wrong:

• Every user is isolated

This kind of isolation only makes sense if you create a DB user for each user and let them directly run SQL queries on your database. You usually don't do that, but instead create an API for pre-defined queries. That API should do the isolation, not some separation of tables...

• MariaDB can query faster

Not really. If you define the indexes properly, MariaDB can easily and efficiently perform user-specific queries. If you program correctly, table-per-user will actually result in slower queries because:

1. You won't be able to use query parameters to specify the user.
2. Therefore you'll have to construct an SQL command string of each query for each user.
3. Therefore your database connection won't be able to cache the queries.

• Easy data analysis

Why? because you don't have to write WHERE user_id = 1234? But now you have to write instead transactions_1234, which might seem a bit easier, but that's only because we are writing SQL directly - in practice the user ID will be stored in a variable, so you'll have to write "... transactions_" + userId + "... instead of WHERE user_id = @user_id(and set the user ID using prepared statement parameters).

And even if you write SQL directly, one-table-for-all-users has other advantages that can help with analysis:

• You can create views that'll help you with the analysis. The views can have a user_id field you can filter by, and MariaDB will be able to do that filter efficiently. With table-per-user you'd have to create a view for each user, which is far less maintainable - even if you never change the data structure you will want to modify the views or create new views.

• You can create SQL functions that receive the user_id as an argument.

Also, when you open the workbench to perform that analysis, do you really want to see 10,000 tables in the table list?

Answer 2

Pretty much what every other dev would do

Because it's the right thing to do.

what if i have 100.000 transactions

With proper indexing, that's not a lot.

MariaDB would have to do much more work

Databases are not spreadsheets. They do not hold the entire table in memory and scan sequentially through it [often].

Most of your 100.000 rows will never be loaded into the server's memory at all; they will sit on disk until they are accessed, via your indexes. Proper indexing strategies will take care of your [perceived] performance problems.

MariaDB can query faster

Yes, it can query an individual table faster, but it first has to locate that table, which means reading through system catalog tables. Also, you still need to index each and every table, which means clogging the system up with at least one (probably more) index on all 100.000 tables.

what if 10.000 users have a table?

Then it's going to take longer to find each table in the system catalogs.

And you're ignoring the thorny issue of those cases where you actually do want to scan across every user's' data (say, finding who's got an overdrawn account).

Splitting your data in this way makes queries like this far more difficult that they need to be.

I will never change the structure

Yes you will.
It might not be for some time, but you will.

I will enhance the security

Users should not be accessing this data directly; only through the application that you provide them, which will take care of ensuring that users are only allowed to see what they're supposed to see.