Page Comparison

One of the most important considerations is making sure that no single tenant can view another tenant’s data.

The most common data scenarios we encounter are.

• A single database containing all tenants and the database has a tenant ID associated with it.

• A single database containing all tenants and the database tables have a tenant ID associated with them.

• A single database containing all tenants and each user connects to the database with a separate user logon, which restricts the data within the database layer itself.

• A single database, that contains a separate schema for each tenant.

• A separate database for each tenant.

• A single database and each tenant has different tables / views.

Sometimes you will have a combination of the above,  but it is more common for one of the solutions below to be used by a partner.  I.E, do not think that you’d have to apply all the solutions below to implement multi-tenancy, it’s much simpler than that.

Single DB with Tenant ID

A common requirement is to call a procedure as a pre-query before any other query is executed on the database.

Image Added

The pre-query can call package procedures or database functions.  The use of the variable [[TENANT_ID]] allows each individual user account to pass a distinct value.  This is set on the user account.

Image Added

Single DB – Tables have a Tenant ID Column

Method 1:

User Restriction.  This is the most common, and probably the simplest way to apply tenancy within a database.  Here you can use any column, or combination of columns within a single or multiple data connections to restrict data.

Image Added

All queries run against the data connection with a user restriction will be forced to show only matching data.  The restrictions force an entry in every query WHERE clause.

User restrictions also allow you to view lists or groups of data.