TLDR: Which direction you create Relationships has a physical impact on your database
TLDR2: A non-tenant, small app prolly doesn't need to worry about this. My interest comes from planning a high-longevity heavy user app, coupled with my nerdiness (sic)
V: 8.1
Windows Server 2012r
SQL SERVER, Query analysis using SQL Profiler
So I'm talking to Bruce one day and we were talking about hitting a BO_REF table from SQL. And I said, "the left one is the parent... "the one" and the multiples are on the right". (Thats how I remember the field layout for SQL access)
Tenant # 1 OWNS 3 Users
Tenant # 750624 Owns 1 User, and a bunch of Leads
He said, "hold it, the many might be on either side" and I replied that mine were always on the left.
In a particular example that I described he said "why would that make a REF table - thats only for many-to-many relationships"... which is correct according to db design as described here:
I said "I dunno. I have 1-to-Many relationships and they always have a REF table." A Tenant owns a Customer, and that Customer can ONLY be owned by 1 Tenant. 1 to Many... so WHY A REF TABLE?
Had a chat with Vlad. I used Bruce's words: "If its not a M-to-M relationship, then it shouldn't have a REF table."
Vlad replied "Not necessarily. I cannot remember the exact rules in the code, but sometimes a One-to-Many will make a REF table based on certain conditions [which I cannot recall now off the top of my head]"
And, following his suggestion, I performed some tests.
It involves WHICH DIRECTION you define the Relationship.
When I started AIM and was getting a handle on the way you linked tables together, Mark Bailey told me he usually connected the BOs a certain way (can't remember now years later).
We know it can be finicky - like if you do a Save of the BO, you can't come back and change the Rel FROM a Peer to Owner/Owned... unless you delete it and re-add the Rel... which may trash all your Grid widgets if you've already built screens and are doing this some point later in development.
So, for myself, I Always do this method (and thats why I always have REF tables):
1) Make a Contacts table.
2) Make a Customer Table
3) Go to the Parent... the Customer table. ADD om_Contacts, Type Contacts, Multiple, Owner.
3b) CREATE Matching Attr, ob_Customer
THIS RESULTS IN CREATION OF 3 BOs:
Customer
Customer_REF
Contacts
But if you build the relationship from Contacts UP to the Parent,
THE REF TABLE WILL NOT BE BUILT
I converted an application last night AWAY from the REF TABLE structure.
Deleted the om_ field. Thats automatically deletes the matching field in Child.
Went to Child and added Rel from there.
1) No change in any programs were necc.
2) New fields were created [internally] in the Child to point to parent (the REN, RID, RMA).
3) REF Tables were removed.
ONLY PROBLEM was that my entire system got trashed when I published.
Something happened (because Aware got confused) as it needed to delete the REF tables and make the Trio of RRR fields, and during that it gave me an error halfway thru publishing. Problem is, that when you re-Publish (Pub2), you can't because now more errors happen because changes that happened during Pub1 were not "undone", so as it tries to re-execute the exact internal process, it can't because fields were already added (object Cust_REN already exists) or the REF table was already deleted (non existent object Customer_REF). It was a REAL mess. I had to manually delete fields, drop-recreate tables, etc. for the Publish to finally execute 100%. Luckily, all these tables were empty. Would have been a real mess with data - and if you restored a backup you'd still get the same Initial error from Pub1. Took about 30 minutes and you had to know SQL.
OK, so why would this matter to anyone?
I have a VERY valid case. (I noticed this when investigating my Query grid that was dying because a user added a 12-meg super-hi-res Image in her RegularUser data. Since she was the primary contact on many recs coming back from the Grid, the ENTIRE RegUser table was being retrieved from the db (12Mb plus each). But I digress.)
My MAIN table in our Multi-Tenant app is the Lead table.
A Tenant OWNs Multiple Leads.
A Tenant OWNs Multiple Users.
Users are constantly hitting BO Lead to see who to contact today.
I know this was using the Ref table - which means Aware [internally] has to Query the REF table FIRST, to see who are the Leads for the current Tenant (it gets IDs from the REF table), then has to retrieve the Lead data for each ID going into the Grid. (It will only get the Pagesize, which means 25 or 50 recs, not the entire set. Gets Next 25 when you go to next page.) (PS_ It doesn't do a physical SQL "JOIN" of the REF table to Lead. It Logically joins them - getting the IDs needed, then retrieving each Lead specifically by ID.)
OK, as a tribute to the Aware team, this all works great as we've all seen - but haven't even been aware of the complexity under the hood.
BUT, In my case, since I knew this was happening, I ALSO ADDED a ps_Tenant to each Lead.
I thought I was being cheeky.
The Query TO SHOW LEADS would usually be this:
SHOW Lead WHERE Lead IN LIRU.ob_Tenant and Lead.Active = 'Yes' ORDER BY x
(Basically, we know the LI RegUser, who points to Tenant A, so show me Active LEADs belonging to Tenant A)
So I thought I'd outsmart Aware.
I changed the Query to:
SHOW Lead WHERE Lead.ps_Tenant = LIRU.ob_Tenant and Lead.Active = 'Yes' ORDER BY x
See what I did?
I thought I would speed up my entire App by having Aware "ignore" the REF Table, because I wasn't using "IN" and Aware's Relationships in my Query. Now, I was hoping that a simple "fast" hit on an indexed field should result in a single-table query. After all, I'd rather do it now than a year down the road when performance was becoming an issue.
But in the analysis of that 12-meg Image fiasco, I realized Aware was ignoring my wishes. I double checked my Grid queries and they were the "new" style, yet Aware was still requesting data from the REF table and performing a multi-table query to fill my grid.
So Aware outsmarted me because it thinks the REF table is the sure bet - and if I had a true M-to-M design it would be.
As it stands, I now need to convert my main table AWAY from the _REF structure as described above.
IN CONCLUSION, I hope this makes some of you more in tune with the intricacies of design in Aware.
While it may not matter for many if a REF table is in your structure or not, for robust, hi-demand apps, this extra level of data abstraction may be a factor in performance in your systems. AND, while I've been able to manage it, having an UNNECESSARY REF table creates 1 more extra table to mess with when using SQL to CRUD the database.
jaymer...
