Working with Database Queries in a multi-tenant web app

Server Side Components General
#1

Hi,

I am building a crm application that is using a multi-tenant single database (Azure SQL). I am trying to find the most efficient way to use the server side actions and database queries to ensure data is secure.

Current I have the database setup with the below as each user can only be assigned to one account, but one account can have multiple companies. I want to make sure that each user can only see the companies assigned to their account. I’m currently using the company ID as the unique id across all other tables.

User Table
userID (used as the identity for the security provider)
accountID

Account Table
accountID
userID
companyID

Company Table
companyID
accountID

When making a query I am joining the IDs together but getting stuck on adding the right condition to ensure only the correct Company data is being returned. I’ve tried using the userID equals identity from the security provider but doesn’t work.

Is it possible to make a database query to the user’s account details and get the companyID then pass this value to the next database query as a variable and set the condition to equal the companyID on the tables witht he data I’m quering?

I can’t seem to find how to use multiple queries in a single server side action.

Hope you can help, as I feel like I’m missing a simple logic step that ones someone says it, it’ll make total sense.

#2

In principle, all field names in a relational database should be unique to ensure that there are no problems down the track.

For example:

  • Users: userID
  • Accounts: accounts_userID

After modifying the field names, the query should work.

#3

Good practice: Use “id” as PK for each table. Use “tablename_id” as FK

User
id:
account_id:

Account
id:
user_id:
company_id:

Company
id:
account_id:

#4

Just query the accounts table with a single record query based on userID = securityprovider.identity then query the companies table using the account ID returned by the first query,the values of which will be available via the picker. It can actually be done in a single query quite easily rather than using two.

3 Likes
#5

Thanks to all the responses, they all helped to make it clearer to me and was able to update my database schema and also queries and it’s working nicely now!

1 Like