Designing a SaaS on a single database, multi-tenant (SQL)

Question

I'm working on a SaaS product and trying to figure out the best way to design the database for my scenario, which I think is pretty standard.

I should not that I don't have an experience designing such a database.

I tired researched online, but there isn't really any info I could find about implementation. There are quite a few comparing the different multi-tenant architectures.

For the multi-tenant approach, I decided go with a single database - seemed to be the most fitting.

Here's the basic list of what should be supported:

• Multiple clients, all separated, no sharing of data between them.
• Each client has it's own user base (staff/employees).
• The client's staff members have different access levels to the system (exposure to different areas, ability to perform certain actions)
• Each client have it's own customers.

I can wrap my head around the basic concept of having the tenant_id on any table belongs to that tenant. I guess my issue is more with how to combine it with different access levels per client's staff member.

How would you go about it? Any reference some implementation of such a DB?

Thanks

---

Update

After @dmfy answer, I gave it some thought and came up with this solution:

account
 -id
 -name

user
 -id
 -account_id
 -username
 -password

role
 -id
 -account_id
 -name

user_role
 -user_id
 -role_id

access
 -id
 -role_id
 -name

role_access
 -role_id
 -access_id

session
 -account_id
 -user_id
 -token

I'll explain-

The role table is essentially a "group" of users associated with a list of permissions/access levels.

The access table represents a single permission. An area of the platform, an action that can (or cannot) be performed.

A row in the session table is created after a successful login. Each time there's a call to the server, if the user has been verified against the token, I will lookup the roles for that user (using the session.user_id on the user_roles and collect it's access list using role.id on role_access.role_id).

Once I have the access list I can check against the request and see if the user is permitted to perform the action.

• Notes
  • role can be customized for each tenant/account (e.g one can have "Management" and "Employees" and another can have "Management", "Support", and "Sales" ), hence the association with account.
  • access on the other hand, is platform-wide. The platform have the same set of areas and actions across all tenants. So there is not need to associate it with a specific account.
  • An improvement to the access lookup could be to store the access list on the session on login, to eliminate the double join (get all the user's roles, get all the roles' access lists).

Questions

• Firstly, what is your overall opinion on the design. Do you see any flaws?
• Is saving the account_id on the session really needed/a good idea?
• Is having the server check whether the user has access to a certain resource is the standard way of doing this? Is there a way to do this as part of the itself query (e.g get an error from the DB itself)?

Answer 1

You might get a better answer by describing the requirements before you outline the solution.

Your design seems to describe an authorisation scheme. It looks fairly credible - I'd summarize it in natural language as:

• A tenant is an account.
• An account has many users.
• A user can have many roles.
• Roles grant access to many permissions.
• The system maintains a list of sessions, mapping requests to users; this in turn allows the system to check whether the user has permissions for a given action.

Without knowing your requirements, that seems fairly reasonable. You may want to include a link from "account" to something your application recognizes as "tenant".

The big question is how you will use this data in your application. Checking permissions - especially fine-grained permissions - for each request could be expensive.

The specific solution here depends heavily on your application framework - many have built-in authentication/authorization models, and it's usually a good idea to use those built-in features.

For ideas on how to implement this, you could look at CanCanCan, an authorization framework for Ruby on Rails, or Authority for Laravel.

It's also not clear how the actual data in your system will be linked to an account - if your system tracks widgets, does the "widgets" table have an "account_id" column? If so, how does your application track who is and is not allowed to access that row?

Answer 2

It sounds like you're conflating database users with application users. In a SaaS product with a shared-schema model, individual users won't have direct acess to the database; instead, your application connects as a single user with appropriate rights on all objects it needs. What you're worried about is what areas of the application users can access and what actions they can take. This means you need to build your authorization model into your schema.

The simplest possible model has two levels of access: regular users and administrators. This can be represented with a users table having a tenant_id to associate individual logins with the correct client, and an is_admin flag. Your application then ensures that only users with the flag set can access administrative functionality. If your authorization model is more complex, design your schema appropriately (users may have a many:many relationship with roles, for example).

Note also that a tenant_id column is only strictly required for tables directly related to tenants; if you have a profiles table with a user_id, you can trace the relationship back to the tenant through users. In some cases it may make sense to add the tenant_id to avoid long join chains.