SaaS ways to access multiple databases and user authentication mechanisms with master database

Question

I'm in the process of building a saas application with a master database for all transactions and the user base and seperate databases for each tenant. Each tenant is given an unique sub domain and based on that, the correct database is pointed to the tenant. The web application is built using php and MySQL is used for data storage.

I have couple of problems and to begin with;

1. When a user visits the tenants home page (E.g. Sub.abc.com), there will be a login functionality available. Since the username password and the user base is in the master database, how would I authenticate a user? Is it through a seperate database connection for the master database or via web apis? What is the best way?

2. There will be roles created by a tenant. So assume the staff for a specific tenant is retrieved by calling a web api and the role is stored in the tenant database along with the user id. Is this the right way forward because there is no direct foreign key mapping and also once the users are retrieved should I store in the tenant database or just hold it in memory?

3. In case the business owner wants a report about something specific for each tenant database, what would be the best way to grab all the data from each and every individual tenant database?

4. How can we capture each tenants database usage, file storage and show in the super admin?

Answer 1

how would I authenticate a user? Is it through a seperate database connection for the master database or via web apis? What is the best way?

Be careful with the "best way" questions - this would point this question towards being off topic as opinion based.

If your application is completely closed source (which I imagine it is), then it doesn't matter. You can access your master database directly if you want to, because all of your client implementations will be part of your overall software package.

That being said, for scalability and maintainabilty it's probably a better idea to build an authentication API interface and use that, so you don't need to manage the database connection component of your client software.

...the role is stored in the tenant database along with the user id. Is this the right way forward because there is no direct foreign key mapping and also once the users are retrieved should I store in the tenant database or just hold it in memory?

This is fine. The users are common to all of your implementations, but the roles are unique to each client implementation. This indicates that the way you're approaching it is correct in that you authenticate first, then assign the client's role to the user once logged in. You should check this against your local database (obviously), then assign it to the user's session so it sticks around for the duration of their visit.

In case the business owner wants a report about something specific for each tenant database, what would be the best way to grab all the data from each and every individual tenant database?

This is too broad to answer. You should also be careful here, because the client's data may not be yours to report on!

How can we capture each tenants database usage, file storage and show in the super admin?

Again, quite broad, but I assume that you could achieve something like this by querying the MySQL server for database statistics on each client database and do the same for the file storage by querying the server that hosts the client's files.

---

I hope this is helpful, please narrow down your questions a little more if you'd like more specific help.

Answer 2

Be careful with the "best way" questions - this would point this question towards being off topic as opinion based.

If your application is completely closed source (which I imagine it is), then it doesn't matter. You can access your master database directly if you want to, because all of your client implementations will be part of your overall software package.

That being said, for scalability and maintainabilty it's probably a better idea to build an authentication API interface and use that, so you don't need to manage the database connection component of your client software.

Agreed. See the thing is for these kind of scenarios it is very difficult to find answers especially on multi-tenancy.

What do you mean by closed source?

If i can have authentication APIs to retrieve data from the master database, how would i host it in a way only the access is granted within the application and outside parties cannot access it? Later on we need to have services exposed to outside users as well. In this case is it better to expose this from now on or work on separate services later? What are the best neat PHP web service libraries do you know other than NuSOAP?

This is fine. The users are common to all of your implementations, but the roles are unique to each client implementation. This indicates that the way you're approaching it is correct in that you authenticate first, then assign the client's role to the user once logged in. You should check this against your local database (obviously), then assign it to the user's session so it sticks around for the duration of their visit.

That is correct! The users are common to all implementations and this is stored in the master database and not on individual tenant databases.By roles i meant, per tenant application, the tenant can create roles which are within the tenant application such as the billing unit, accounts, front desk, etc. So these user_id and role_id is stored in the tenant's database. That's why i asked since the user_id is not directly mapped to the master database's user table if it's okay.

This is too broad to answer. You should also be careful here, because the client's data may not be yours to report on!

For now let's assume the super admin wants to know roles of every tenant.In this case how would i retrieve the data from all tenant databases? Do we use Views for this or since we are using mysql how can i achieve this?

Again, quite broad, but I assume that you could achieve something like this by querying the MySQL server for database statistics on each client database and do the same for the file storage by querying the server that hosts the client's files.

Extremely sorry about this. We basically want to track the usage of tenant databases to see if they exceed the amount we agreed on. That's why. I hope there is a way to achieve this.

Also, for the site images, is it better to store on the hard disk or in the database as BLOBs? End of the day we need to think about Scalability as well as a way images will load with less bandwidth consumption?