B4J Question [ABMaterial] Multi tenant database architecture

[unitybyte]

Hello!

I am designing a saas app that is going to be made with b4j and abmaterial.

What I am currently spending my time thinking about is the database architecture.

currently in my testing I am using one sigle database with a user_auth table to authenticate users against, and a single database for all the users where
each user is separated by a tenant_id field in the tables.

As my application really should have more data separation than that, I would like to have separate databases for each client/user.

Now, I have tested somtehing like this, where I use a single database connection, authenticated by a user that has acces to a user_auth database and all the
user databases.

When a user is logged in, the session stores the name of the database, and I am using this databasename to separate the users in every SQL queary I use against the database server.


My question is: Is this a good enough aproach when I want to separate the data?

Or should I use two db connections:


Connection 1:
This will be used to connect directly to the auth database with a database user who has access to only the auth database.

Connection 2:
This uses the info gathered from connection 1 to establish a connection directly to the users database.

I hope this question makes sense :O

/ronny

 

[keirS]

What DBMS are you using? The problem with having many DBs is the that each DB will create a memory based cache. So using a DB per client will require far more memory than having a shared DB..

 

[unitybyte]

I am currently testing with mysql.

Somr issues I see with a shared db, is scaling when growing, and if the db is corrupted, it affects all the clients.

I might be willing to pay the cost of memmory versus less risk for affecting all clients.

Is there some obvious way to scale a shared db aproach?

 

[alwaysbusy]

is scaling when growing,

What numbers do you have in mind? jServers connection pool (or now the new High Performance Connection Pool https://www.b4x.com/android/forum/threads/hikaricp-high-performance-connection-pool.88430/) can handle many thousands of simultanious connections to the database.

 

[unitybyte]

H

What numbers do you have in mind? jServers connection pool (or now the new High Performance Connection Pool https://www.b4x.com/android/forum/threads/hikaricp-high-performance-connection-pool.88430/) can handle many thousands of simultanious connections to the database.

I have no idea yet, but I want to design for growth. When I say scaling, I mean the size of the shared db, and access times when that db get increasingly bigger as more clients use it.

Am I overthinking this?

 

[keirS]

I am currently testing with mysql.

Somr issues I see with a shared db, is scaling when growing, and if the db is corrupted, it affects all the clients.

I might be willing to pay the cost of memmory versus less risk for affecting all clients.

Is there some obvious way to scale a shared db aproach?

Use clustering technology. If you were looking at massive scaling then an Amazon RDS Aurora cluster or an MS Azure SQL server cluster. That would also solve your corruption worries.

 

[unitybyte]

Use clustering technology. If you were looking at massive scaling then an Amazon RDS Aurora cluster or an MS Azure SQL server cluster. That would also solve your corruption worries.

Thanks. Thats way off in the future though. What its about now is to make the right choices, so I dont regret them later