Guidelines to store tenant specific information in a multi tenant architecture

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.

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

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!

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

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:

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!

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, thanks for the help!