With a single database strategy all of the tenant data is put into a single database
TenantID columns are added to the tables, so that the data can be filtered on this column
Filtering can be done using Global SQL filters
The Data Dictionaries are extended to automatically fill in the TenantID when saving a new record
Relationships will not be created between the data tables and the tenant tables in an effort to keep the DDO structure simple
Shared tables and tables that are just for linking data do not require a TenantID as a column
When using Global SQL filters…
Do not accidentally override/deactivate the global filters
pbApplyGlobalSQLFilter must always be set to true
If not, the local data dictionary SQL filters will override the filters
This is typically set to true using a subclass of the DataDictionary
Make sure that DF_FILE_SQL_FILTER_EQ is set true
Demonstration
For this demonstration, an example workspace called ChinookMultiTenantSingleDB will be used. This is a music database that contains many tables that include items such as artists, albums, tracks, etc.
There is also a tenant table, which initially contains three tenants. During the demonstration data will be constrained by these three tenants. For example, the ‘Track’ table shows that it includes a ‘TenantID’ that matches one of the tenants.
The workspace also includes a Windows application called ‘MediaStore.src.’ Its main view is ‘Albums,’ which shows albums with the related artists and tracks. MediaStore.src also displays an additional combo, which shows the available tenants. This allows different tenants to be selected while using the application.
Running the application shows the combo that displays the different tenants that available for selection. Opening the ‘Albums’ view should only show data that applies to the selected tenant.
In the DataFlex Studio, double clicking on the combo displays its code. It shows that the tenant selection menu item, and that on creation it fills the combo list. When an item is clicked it is going into ‘SwitchTenant.’ Drilling into SwitchTenant (right click on it and select ‘Go To Definition’) shows that it is a global procedure that first calls a function called ‘ConstrainTenantDb’ with the TenantId. Then it calls a function that clears all of the views.
Returning to the SwitchTenant function, and then drilling into ‘ConstrainTenantDb’ the constrain database function is shown as a separate package so that it can be reused in different projects in the same workspace. All tables that have a TenantId are shown as open. A global variable called ‘giCurrentTenant’ is defined. When the function is called the ConstrainTenantDb function gets the TenantId and moves it into the global integer. Next, the different SQL filters for the different tables will be defined.
In the DataDictionary, when a new record is created there the Save_Main_File procedure is called that uses the variable giCurrentTenant to make sure that the TenantId is filled in with correct integer.
pbApplyGlobalSQLFilters is set to True to make sure that when this DataDictionary is used with a view that the local SQL filters are extended with the Global SQL filters. This is done for all of the tables.
When a new album and artist is entered into the ‘Albums’ view, the integer corresponding to the selected tenant is automatically applied.
REFERENCE LIBRARY
