Solved

Guidelines to store tenant specific information in a multi tenant architecture

C
Rank

Hi,

 

Are there any guidelines when implementing a multi tenant architecture using Thinkwise. Which means we have 1 database instance for multiple tenants (organizations) sharing the same tables. Each table will have a tenant_id to distinct the owner of the record.

I want to figure out which possibilities there are to store tenant specific information in a table:

  1. Creating for instance 10 extra fields that can be used by the tenants is probably not really a good option in Thinkwise as I've not found a way to have context/tenant specific translations.
  2. Creating tenant specific fields in the base tables is probably also not the best option if we have for instance 5 tenants that want to use 10 additional fields. which means we have to create 50 extra fields in he base tables

Hope to get some good ideas from theThinkwise community.

 

Thanks!

icon

Best answer by Mark Jongeling 17 May 2022, 13:20

View original
This topic has been closed for comments

6 replies

Mark Jongeling
Rank
Userlevel 7
Badge +23

Edit: See later reply for better answer

Hi!, 

I happen to have some experience working on a project that also is using Multi-tenancy within one database, so I hope my answers can help you out.

  1. Creating for instance 10 extra fields that can be used by the tenants is probably not really a good option in Thinkwise as I've not found a way to have context/tenant specific translations.

Creating model fields will mean they have a model translation by default. This is of course not handy if these fields are used different between Tenants. What you for instance could do is use a Formlist and hide any fields that are not applicable for the Tenant.

In front of the extra fields, you can place another field using the Label control. This will cause it to look like a Label while actually it is a value you can select using an Expression column. This could then be used to show the Name of the Tenant field. However, in the Grid this doesn't work, hence you either need a Form or Formlist.

  1. Creating tenant specific fields in the base tables is probably also not the best option if we have for instance 5 tenants that want to use 10 additional fields. which means we have to create 50 extra fields in he base tables

There will be a limit as you'll need to specify how many fields are available for tenants to use maximum. However, this could all be done in a View. This view then has a couple of Primary key columns, and for instance 20 Label-columns and 20 Free-columns. These can obtain data from a Table that also has a Tenant_id.

You can make use of PIVOT. With Pivot, you can change a row-based data set into a column-based data set. This means that you can save 20 rows of Tenant-specific data, then Pivot and assign that (previously row-data) to columns. Now all 20 rows are supplying values to 40 columns. With a Layout procedure, you can hide any fields that shouldn't be visible.

Hope this helps!

"Wake me up before you low-code"
C
Rank

Hi Mark,

 

Thanks for your reply!

When using the pivot solution: what type should the free-columns be? This probably has to be a varchar type to keep flexibility? So you have to create extra logic to check the input that is given by the user? For instance should we store date information in the varchar field?

 

I've also been thiking that the most simple solution is to create extra tenant specific columns in the base tables when needed and provide them with a prefix of the tenant. These extra fields should standard be hidden and not mandatory in the datamodel. They should only be made visible in the user interface for the specific tenant. The translation will also not interfere because every field will be unique.

When a tenant wants to store extra information in the base table this can also be discussed with the other tenants. If it's usefull for all them then we create a column that is visible by all of them. If it's only needed by 1 tenant than we create the column with the tenant prefix. I\m guessing this will keep the datamodel organized. Am I missing something in the long run?

 

Kind regards,

 

Charif

 

Mark Jongeling
Rank
Userlevel 7
Badge +23

Hey Charif,

what type should the free-columns be?

That would indeed be the case. But you could also make about 7 fields (Alphanumeric, numeric(X,2), number, Date, Time, Datetime, Lookup(?)) and show the appropriate field on the basis of the given datatype by the Tenant. 

With a fresh cup of coffee in hand, I think the Pivot is not needed in this case actually (luckily 😂). When making use of a Formlist, you can create a row per tenant specific field. Using a Layout procedure, you can then hide all fields that are not applicable based on the datatype.

For instance, did the tenant add a field called "Emergency number” with datatype "Alphanumeric”. then show only the following fields: field_label, field_alphanumeric

The table would look something like this:

Tenant_specific_field:

Name Datatype Nullable Remarks
tenant_id int/bigint/varchar No  
field_label (n)varchar No  
field_data_type tinyint (combobox) No  
field_alphanumeric (n)varchar Yes  
field_numeric numeric(16,2) Yes  
field_date date Yes  
field_time time Yes  
field_datetime datetime2 Yes  
field_lookup int Yes May be difficult due to lookup complexity

 

This way there's no need to dynamically alter your data model or perform tricks to make it work. This solution can be used across tenants and can “infinitely” grow.

Hope this helps!

"Wake me up before you low-code"
C
Rank

 

Hi Mark,

 

For my understanding…

 

Doesn't the table from your example need a primary key and a column to store the name of the base table?

In the end the tenant specific fields should be shown only for specific base tables.

For instance: I only want to show the emergency_number field in the customer table. And not in the purchase_order table?

 

I also then have to join the base table and the flexible field table from your example in a view? And have the view presented in a form?

 

Kind regards,

 

Charif

Mark Jongeling
Rank
Userlevel 7
Badge +23

Hey Charif,

You could indeed make it so that the PK consist of the table name where it should be shown. In the SF you can dynamically create references to this table with columns: tenant_id, table_id (or something similar). Then each table that has a reference with this base table, needs an expression column containing the name of the table. This way you can ensure that the detail reference goes to the correct table.

(new) Tenant_specific_field:

Name Datatype Nullable Remarks
tenant_id int/bigint/varchar No  
base_table varchar No Must be an existing table in the data model
field_label (n)varchar No  
field_data_type tinyint (combobox) No  
... … ... ...

But alternatively you can also decide for the tenants where they can and cannot add their own custom fields. Then maybe designated base tables are more suited.

Hope this helps!

"Wake me up before you low-code"
C
Rank

 

Mark, thanks for the help!

Terms & ConditionsCookie settings