Tenant relationship best practice question

If you have questions or if you want to share your opinion about Aware IM post your message on this forum
Markfre1
Posts: 221
Joined: Sun Mar 11, 2012 10:15 pm
Location: United States

Tenant relationship best practice question

Post by Markfre1 »

The app is going to have hundreds of tenants.

The question is I am putting the tenant relationship in each object as 'owner of'.

But a Customer is owned by the Tenant and the customer has references to address, invoice header, lines, etc. which owns those.

If the tenant is deleted, the customers will be deleted, and because the customers owns the address file, etc., those should be deleted as well.

The question is that the tenant also references the address, invoices, etc. Mainly for 'protect' and easily finding things by tenant. I think I should make the tenant and owner of every object, and not a peer relationship for sub objects such as address file. Is there any advantages or disadvantages to either, or any other suggestions.

All tenants will be in one database (MariaDB). (Side note, if you use scripts adding records, you can get MariaDB to handle the BasIDGen file perfectly with one statement, MySQL cannot)

Any advice on these relationships is appreciated. Thanks.

Mark F
ACDC
Posts: 1138
Joined: Sat Jun 30, 2007 5:03 pm
Location: California, USA

Re: Tenant relationship best practice question

Post by ACDC »

The app is going to have hundreds of tenants.
So the tenants are going to be users ?

I would set up an organizational structure and have the tenants assigned to all elements of the Organizational structure in a PEER relationship. Having the tenants in an Owned relationship could get messy

The organizational structure is where the Owned relationships must be

Example of an Organizational Structure

Org
Company
Division
Branch

(Org owns Company Owns DivisonOwns Branch etc etc)
Every object is assigned to a tenant user which in turn belongs to the ORG

To further enhance this, every object has 4 Text attributes e.g. Org,Company, Division,Branch that gets updated with the org structure based on the assigned user (handled by an object rule). Sort of like stamping each record in the database with the org details sourced through the Object.AssignedTenant or LoggeddInUser.

and then running the PROTECT rules based on the anyone one of the Text Org attributes

hopefull I am making sense, without knowing much about your app this could give you some ideas
Last edited by ACDC on Wed Aug 07, 2019 3:17 pm, edited 1 time in total.
Markfre1
Posts: 221
Joined: Sun Mar 11, 2012 10:15 pm
Location: United States

Re: Tenant relationship best practice question

Post by Markfre1 »

Yes, a tenant can be a user. (but only one per company)

But the system is unique. Just to get a feel for what is going on, see below.

All users have a Detail and a Main object. The Main object is the login, and the Detail is tied to a particular tenant.

For example, a CPA is invited to work in a company (tenant). But think of QuickBooks. A CPA might be invited to work in 50 differerent companies but you do not want 50 different logins.

So what happens, is when the CPADetail is created by a Tenant, it looks for a CPA Object and if it does not exist it creates one which becomes the way the CPA logs in. When the CPA logs in, he sees all his CPADetail (tenants) he is attached to. So one login, sees all tenants.

I did this for customers, staff, contractors, etc. But believe it or not I forgot how I handled the tenantdetail as I did it a year ago. But it should work the same. A TenantDetail is the user who is attached and creates a tenant company. Then Tenant will have TenantDetails. So basically the Tenant should have 1 login, show all his tenant details which is a company. Just revisiting this now as all the work has been creating the tenantdetail and the rest of the app.

So to answer your question, yes the tenantdetail is a user for a particular company but there can only be one, like an administrator.

So if the tenantdetail is deleted then the company and all objects should be deleted. And if the Tenant main object is deleted then all tenants should be deleted.

I guest the questions comes down to if I want to remove all records for a company, is it ok to have everything owned by the tenantdetail, or let the ownership of the sub objects delete on there own. My feeling is the TenantDetail should own everything, but did not know about overhead, best practices.

One last thing. at one time I was relating Invoice lines to the Customer, and Vlad said, the customer should relate to the header and the header to the lines, and do not relate the lines to the customer. I think this case is different.

Thanks Mark F
ACDC
Posts: 1138
Joined: Sat Jun 30, 2007 5:03 pm
Location: California, USA

Re: Tenant relationship best practice question

Post by ACDC »

sorry didn't see yr reply and edited my original response
customaware
Posts: 2391
Joined: Mon Jul 02, 2012 12:24 am
Location: Ulaanbaatar, Mongolia

Re: Tenant relationship best practice question

Post by customaware »

IMHO ALL BOs that ultimately are uniquely related to the Tenant should be OWNED BY some BO whereby you can navigate all the way back to the Tenant via Owned References only.

I Structure as follows.

Tenent - Not SystemUser
Administrators/Coordinators, Users, Customers, Employees are ALL Owned By Tenant
Entity Owned By Tenant
Department Owned by Entity
DepartmentSection Owned by Department

So, in my system.... if I really needed to delete a Tenant (and under GDPR requirements you need to be able to at the Tenants request) I can just delete the Tenant and EVERYTHING uniquely related to the Tenent is gone.

Additionally, thanx to the fine detective work pursued by Jaymer..... I now NEVER create an Owns Reference from the Parent. ONLY EVERY create the Owned By Reference from the Child (ob_MyParent) together with the Matching Owns Many for the parent om_MyChildren.

Additionally..... any BO should ONLY EVER be Owned by one single BO. I see many people have BO's owned by multiple BOs. Not only is it illogical... it is very dangerous for obvious reasons.

Additionally, I would have severly struggled if I had not used Reference Pre-Fixes. ob, om, os, pm, ps.

Imagine you a down in the hierachy 8 levels deep and working on some Process.

Tenant
Country (ob_Tenant)
Region (ob_Country)
Entity (ob_Region)
Customer (ob_Entity)
CustomerDepartment (ob_Customer)
DepartmentSection (ob_CustomerDepartment)
SectionResource (ob_DepartmentSection)

Process Input is Tenant and you need to process SectionResources that are related to this Tenant.
Imagine each of the BOs above have many many attributes.
So, to work out how to reference back to the Tenant would be tedious and require you to ensure you were using the correct Reference and possibly scrolling thru 100s of different attributes.

But, if you religiously use prefixes....

FIND SectionResource WHERE SectionResource.ob_DepartmentSection.ob_CustomerDepartment.ob_Customer.ob_Entity.ob_Region.ob_COuntry.ob_Tenant

Doing this using the F3 predictor is a drop dead snap... you only need to remember ob and the next logical Attribute (the exact one you want) is immediately the one you are preseneted with to select.
Cheers,
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
Image
Markfre1
Posts: 221
Joined: Sun Mar 11, 2012 10:15 pm
Location: United States

Re: Tenant relationship best practice question

Post by Markfre1 »

Mark, excellent post. That is the way I pursued it today. However, do not have too much of a problem creating the relationships from the tenant. But I did have some issues previously where the data ren, rid, etc did not get created when the object was created.

That is something I better look at. I remember having to delete relationships and re-create. I hope it's not a problem, but its something to look at.

As a note: I guess there are times where you work through the hierarchy, like Customer to InvoiceHeader to Invoice Lines and back. But I prefer a direct link to the tenant as you say so when the tenant is deleted it deletes all objects.

Thanks again for the feedback. Just was concerned having 30 plus ownerships from the tenant may not be a good practice, but in the scheme of things, my number of Objects is small. Just a lot of tenants.

Mark F
customaware
Posts: 2391
Joined: Mon Jul 02, 2012 12:24 am
Location: Ulaanbaatar, Mongolia

Re: Tenant relationship best practice question

Post by customaware »

Creating from the Parent automatically creates a reference table in the DB which is unnecessary and it adds to complexity when you are trying to work out what is going on.

I use it as a Golden Rule now......ONLY EVER CREATE A 1:M reference FROM THE CHILD TO THE PARENT.
Cheers,
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
Image
Markfre1
Posts: 221
Joined: Sun Mar 11, 2012 10:15 pm
Location: United States

Re: Tenant relationship best practice question

Post by Markfre1 »

Thanks. Good to know. Mark F
Jaymer
Posts: 2430
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

Re: Tenant relationship best practice question

Post by Jaymer »

eagles9999 wrote:and it adds to complexity when you are trying to work out what is going on
Esp. when you also want to access data externally from Aware.
ACDC wrote:and then running the PROTECT rules based on the anyone one of the Text Org attributes
This may have gotten overlooked... remember there are 2 ways to enforce that a Tenant only sees its data:
a) in code, all your FINDs have statements in them to restrict to a specific Tenant data value
b) Using READ PROTECT as is shown in Vlad's video.
I don't know which is the current "best practices" method.

There are ways to satisfy GDPR requirements (and also in general) to "clean up" child records if they need to be deleted, and thats with SQL code. Mark doesn't do SQL, so you'd have to weigh that into his decision to have Aware manage it all.

Also, re-examine this scenario from Mark:
Tenant - Not SystemUser
Administrators/Coordinators, Users, Customers, Employees are ALL Owned By Tenant
Entity Owned By Tenant
Department Owned by Entity
DepartmentSection Owned by Department
SectionResource

If you want to access a list of DepartmentSections (for a report, grid, picklist, whatever), you'd have to use Marks statement:
SQL: FIND SectionResource WHERE SectionResource.ob_DepartmentSection.ob_CustomerDepartment.ob_Customer.ob_Entity.ob_Region.ob_COuntry.ob_Tenant

So, when you create a Tenant, their Tenant ID is never going to change (beit a ID # or your own TenantCode).
There's a LOT of overhead in this above SQL to find the SectionResources for TenantID:1001

So, in some cases where I know a particular query is being hammered constantly, I'd put the "never going to change"-TenantID down in that data file. Then the FIND statement doesn't really have to join to any extra tables to get its records (save the ones needed for shortcuts). An index is put on that TenantID and immediately the db can find the SectionResource records without any extra joins. And then, if you know you have to always join up to the table "above" to resolve some Shortcuts, then that table could have the TenantID in it instead.

Of course a lot of this has to do with the way the backend analyzes queries. No idea about MariaDB, but MSSQL certainly has plenty of "explain plan" and query costing and SQL Profiler data that can help analyze this.

ALSO, Mark's GDPR requirement may not be an issue here in the states, so you may not be "required" to delete data after someone cancels their account. There's plenty of people who do a 'soft' delete and don't really need to physically delete records "AT THAT TIME" - its not like a tenant can't be disabled (like when their bill is past due and you wait for them to renew) and a 30-day routine comes by and clears/archives that data later.

jaymer...
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
tford
Posts: 4238
Joined: Sat Mar 10, 2007 6:44 pm

Re: Tenant relationship best practice question

Post by tford »

I use it as a Golden Rule now......ONLY EVER CREATE A 1:M reference FROM THE CHILD TO THE PARENT.
I would love to see a list of "Mark's Golden Rules"
Last edited by tford on Thu Aug 08, 2019 3:40 pm, edited 1 time in total.
Tom - V8.8 build 3137 - MySql / PostGres
Markfre1
Posts: 221
Joined: Sun Mar 11, 2012 10:15 pm
Location: United States

Re: Tenant relationship best practice question

Post by Markfre1 »

I went back and looked at creating from the tenant versus creating from the child. Interesting and you are correct. Creating from the Tenant does not add the RID, REN to the child which is needed for scripting. It creates a separate file.

So it looks like I need to go back and re-create the Tenant / owned by relationships from the child objects. Thank you for the great input.

Mark F
ACDC
Posts: 1138
Joined: Sat Jun 30, 2007 5:03 pm
Location: California, USA

Re: Tenant relationship best practice question

Post by ACDC »

ACDC wrote:
and then running the PROTECT rules based on the anyone one of the Text Org attributes

Jaymer Wrote:
This may have gotten overlooked... remember there are 2 ways to enforce that a Tenant only sees its data:
a) in code, all your FINDs have statements in them to restrict to a specific Tenant data value
b) Using READ PROTECT as is shown in Vlad's video.
I don't know which is the current "best practices" method.
PROTECT is universal, in my mind its the way to go, its a simple rule on the Object and provides easy management especially in a multi Tennant environment. Sometimes in the FIND....something goes wrong, incorrectly configured queries etc, suddenly expose data unintentionally to other tenants - PROTECT is a universal guarantee - Also the query becomes simpler and is also universal
Markfre1
Posts: 221
Joined: Sun Mar 11, 2012 10:15 pm
Location: United States

Re: Tenant relationship best practice question

Post by Markfre1 »

I use both methods. Customer.Tenant=LoggedInSystemUser.CurrentTenant in which the current tenant is set I believe as a non-persistent object when logging in (did this a long time ago). Every object has for example has if Customer.tenant is undefined, then Customer.Tenant = LoggedInSystemUser.Tenant. All queries, refine by this.

So currently I am seeing only the current tenant. But by having tenant in each object, I can also use the protect.

The protect I think is critical just as a precaution to protect the data from tenant to tenant. As far as overhead goes, etc, I have no idea.

I'm also concerned about having hundreds or thousands of tenants, and scaling but I decided I can only write this app in AIM, and I eventually said I will just deal with it. So its full steam ahead or nothing. My previous app had thousands of users but it was C/S and each data set was seperate so this is a leap to one database and multiple tenants. We'll see. Thanks for the input.

Mark F.
Markfre1
Posts: 221
Joined: Sun Mar 11, 2012 10:15 pm
Location: United States

Re: Tenant relationship best practice question

Post by Markfre1 »

Mark and Jaymer said
"Creating from the Parent automatically creates a reference table in the DB which is unnecessary and it adds to complexity when you are trying to work out what is going on.
I use it as a Golden Rule now......ONLY EVER CREATE A 1:M reference FROM THE CHILD TO THE PARENT."

So I am deleting the relationship and adding it through the child object and then saying test with no initial data and I am still getting a reference table?

Is the reference table possibly getting created from some other relationships in this object? And this is correct? Is there some step I am missing.

Child Object, Create Tenant Relationship from Child, Multiple checked, Owned by Tenant.

Thanks,

Mark F

Mark F
PointsWell
Posts: 1457
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

Re: Tenant relationship best practice question

Post by PointsWell »

Markfre1 wrote:Mark and Jaymer said
"Creating from the Parent automatically creates a reference table in the DB which is unnecessary and it adds to complexity when you are trying to work out what is going on.
I use it as a Golden Rule now......ONLY EVER CREATE A 1:M reference FROM THE CHILD TO THE PARENT."

So I am deleting the relationship and adding it through the child object and then saying test with no initial data and I am still getting a reference table?

Is the reference table possibly getting created from some other relationships in this object? And this is correct? Is there some step I am missing.

Child Object, Create Tenant Relationship from Child, Multiple checked, Owned by Tenant.

Thanks,

Mark F

Mark F

The _REF tables work to support multiple relationship types. Ie if you put it on the 1 side of a 1-m relationship, it also handles the m-n relationships and also if you have a peer relationship that does not have a reciprocal relationship.

Suppose you had a Contact with Countries of interest as a relationship.
This would need BO Contact and BO Countries.
If you make the BO Country aware of its relationship to Contact (and even if you don't) this is a m-n relationship which if you were building the tables by hand you'd need:

Code: Select all

Contact -< contactCountries >- Countries
Aware is building that in the background but in a generic multi purpose table that gets used for Contact / Country and any other m-n relationship in the background.

A way to get round REF tables is to specify specific BOs for the relationship. This has the benefit of allowing you to track the start and end of the relationship which is harder to do with field level relationships.

Eg your Contact BO could have a multi value field for Countries of Interest, adding Australia to that field does not allow you to note when it became active, similarly when Australia ceases to be of interest you can't easily identify when that ceased to be of relevance. By using a separate BO for ContactCountry you can then put DateFrom DateTo values. Only of interest if you need to track slow moving dimensions obviously.
Post Reply