To _REF Table, or NOT to _Ref Table -- that is the Question

[Jaymer]

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

Screen Shot 2018-07-31 at 10.32.37 PM.png (16.37 KiB) Viewed 41915 times

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:

Screen Shot 2018-07-31 at 10.41.24 PM.png (27.18 KiB) Viewed 41915 times

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:

Code: Select all

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:

Code: Select all

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...

[customaware]

Great post Jaymer and long overdue.

At times I forget but normally always try to remember to build any reference from the Child up to the Parent.

But your deep dive and analysis is invaluable going forward.

Cheers and Beers.

[PointsWell]

Another time that AIM can be too clever is when you try to make references to Business Object Groups from another BO. If you don't get the direction right it creates the REF tables.

It is a nightmare to fix it all up and remove them. I still have one REF table that I can't get rid of - fortunately I am in the midst of testing at the moment so I am sure it will eventually drop a reference in there for me to find.

[tford]

Very interesting analysis, Jaymer. Thanks for sharing what you've learned in such a detailed manner.

[BLOMASKY]

Now, I did not spend ANY time testing this like the highly talented Jaymer did, But you only need a _REF table if there is a many to many relations. One to One and One to Many (Parent - Child) do not need the _REF table.

If, you start in the Parent and tell Aware that there are Multiple children and have aware create the matching relationship in the child, I don't believe you have the option to tell the child that there is only ONE parent. So Aware assumes the worst and creates the _REF table.

Even in small systems, the REF file can be a bottleneck (When I say small, I mean 10,000 records in Parent - children tables. It does help to index this table well (both on the parent and the child record and make sure that there are not many dups in the index.

Bruce

[Adrian]

Many thanks for a really useful carefully researched and clearly explained post. I was trying to understand the reason for many to one data being stored in a many to many _REF table rather than directly as a reference in the child table, and this pops up today and explains to me perfectly why they are there. I will take care to define 1:M relationships starting from the child BO which should give a significant efficiency improvement.

[tford]

Jaymer,

For clarity, if the configurator doesn't want the REF tables created such as when your steps are followed:

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

What are the steps to NOT create the REF tables?

[PointsWell]

What are the steps to NOT create the REF tables?

Create the relationship from whichever BO has the one of the 1-M relationship.

If you don't specify that it is the 'one' in the 1-M it assumes both ends of the relationship are 'many'

[customaware]

So,

Create the ob_Parent from the Child BO with matching attribute om_Children.... rather than

Creating om_Children from the Parent BO with matching attribute ob_Parent

[ACDC]

I tested this issue on V7.1 (2240) and it seems to behave like it should. i.e Using an Invoice and InvoiceItem setup, I created the child multiple attribute from the parent and no _Ref table was created.

The only way I could get the _Ref table created, was to create a Peer (multiple) object.

So this issue seems to have crept in on the newer versions, maybe by design ? or maybe a bug ?

[Jaymer]

ACDC - thats very interesting - thx for checking.
I no longer have any v7 to try this on.
Maybe thats how I got in the habit of doing it from the "parent down" (as I view it) and never thought about it back then.
And I wasn't on SQL Server back then.
But now since v8 I'm pretty exclusive (and happy with) SQL Server Express and with SSMS, I saw the REF tables and just learned to work with them.

Could you do one more thing...
migrate that V7 app to V8 and see if they now appear?
jaymer...

[ACDC]

migrate that V7 app to V8 and see if they now appear?

In V8.1 , when publishing the REF table does not appear

However creating a new child object from the Parent, it does create a REF table as described

BUT , I noticed some erratic behaviour when setting the child attribute to Owner Of , it defaults to peer before saving and then if you go back to the setting , it defaults again to peer. I am convinced this is a bug

[PointsWell]

Maybe thats how I got in the habit of doing it from the "parent down" (as I view it) and never thought about it back then.

Interesting, I was in the habit of bottom up as you need the parent to exist before the child (in my head at least).

[PBS]

An understanding of this topic is necessary for our projects, so we applied ourselves here to try to workout what is going on. Essentially we needed to KNOW how to reliably create/modify BOs in a live project so that the relationships were correctly created and the data and forms maintained.

Our finding is pretty much that once a "baddie" config is created it cannot be undone. Sure you can remove the reference links in the BOs and recreate them ... but by then your already created data is toast; not to mention any work you have done on forms is also toast.

I will post what workflow I think needs to happen to reliably create/maintain the right config (BO setup) later in this post.

First the detail of what we found. Our approach leveraged the observations of the other posters to this topic; we confirmed that a relational link created from the CHILD BO did the right thing; that is, did not create the Ref table.

Question is; but why. To find out we exported a small test project to XML (not the usual BSV) and had a look. We figured there had to be something in the xml that managed the TABLE CREATE mechanism.

The short story is that there is an attribute persistable="false" as in the code below that MUST live in one or other sides of the relational link.

If the persistable="false" is on the PARENT then the reference table is NOT created ... put it on the CHILD side and the reference table is created ... simple right.

Code: Select all

  
  <object_definition name="BO_A" id="102" intelligence="none" protection_level="none" attribute_protection_level="none"> 
    <attribute_definition name="om_BO_B" reference_type="BO_B" id="102" persistable="false" multiple="true" relationship_type="parent"> 
      <matching_attr>ob_BO_A</matching_attr>
    </attribute_definition>
...

Code: Select all

  
 <object_definition name="BO_B" id="101" intelligence="none" protection_level="none" attribute_protection_level="none"> 
    <attribute_definition name="ob_BO_A" reference_type="BO_A" id="102" multiple="false" relationship_type="child"> 
      <matching_attr>om_BO_B</matching_attr>
    </attribute_definition>
...

Ok, having gotten in a "baddie" link how do you fix it. Short story is you don't. Exporting to xml; then making change to the persistable attribute and then re-importing does NOT work. The import will work, the conflicts are all good; but running the project will error due to the runtime not being able to adjust the database (or something) to the new config.

We tried dropping tables in the db manager and similar tricks; all to no avail; it seems that the AwareIM overviewer db thingy has its claws in too deep to allow such trickery to succeed ... leastways to my understanding of it.

The solution (as I presently see it) is to keep your main (publishable) project clean. Do the coding work in a separate project; export to XML and have a look; and/or run it and have a look (in whatever database manager tool you have) to see if any ref tables have been created.

If there are ref tables being created:
* Export to xml.
* Using the Configurator, remove the links in your project BOs.
* Run it to ensure the db gets cleaned.
* Open the xml file in a text editor; and move the persistable="false" to the PARENT side of the link.
* Import the fixed xml into the project; this will re-create the db properly.
* The import of the fixed xml will preserve any forms with the links you had worked on.
* Run the new project to create the db tables.

Once you are happy your TEST project is a goodie; then export to xml (or bsv) and import that into the live project.

Anyways this is my 2-cents worth on the topic other than to say ... "THIS CAN/SHOULD BE DONE BETTER" ... the persistable flag should be exposed, and a change to the flag should migrate the data to the new format ... it is a computer program right.

Norman

[Jaymer]

Norm, I can "feel" Vlad cringing now... LOL
... with thoughts of modifying the XML and re-importing.

I mean, I've thought of modifying the XML a lot, and re-importing, but your "persistable" change is bigtime!
Anyways, thanks for the research.

I'd like to ask this... are you agreeing with me that the _REF tables being used when not really needed is unneeded overhead, esp. for a big heavy-use project? Thats the direction I was going with it.

And I do intend to fix my app because my main Tenant file OWNS SIX (6) tables, all managed via REF tables... and All are incorrect.
It may break some Forms, but won't alter any procedural code. I can add some 1 field in all 6 child tables to record who the owner is, and after the 3-system fields are created (REN,RID,RMA) I can easily populate them from the saved owner ID.

I guess I just learned the WRONG DIRECTION to create relationship tables... 50/50 shot and I came up wrong.