Propietry Database Design change utility

ACDC

AwareIM has its own proprietary way of configuring a database with regard to Primary Keys and references. This makes it impossible or very difficult to use third-party reporting and BI tools.

Does anyone know exactly where the differences are and how one would go about modifying an AwareIM-based database to be more compatible with a standard DB design so it can play nice with other tools?

I am thinking along the lines of mirroring the AwareIM database and then programmatically via stored procedures and triggers to reconfigure the DB setup. This sort of happens in real-time during the mirror process. I doubt this sort of dual compatibility can be done from inside AwareIM. Maybe the concept is just too difficult

Any ideas or clues as to how one can achieve some level of compatibility with a standard DB setup?

BLOMASKY

What aware does that is different from many other tools is that foreign keys are not defined in the database. Most reporting tools will, by default, use foreign keys to read data from multiple tables. It is easy, and will not break aware for you to manually create foreign keys. In aware the "many" table will have a field that ends in '_RID'. (the _RMA and _REN columns can be ignored). You can create a a "boiler plate' script to create the foreign keys. After that, any reporting tool can easily read the linked files.

Something like this:
ALTER TABLE [dbo].[Orders]
ADD CONSTRAINT [fk_OrdersToCustomers]
FOREIGN KEY([ob_Customers_RID])
REFERENCES [dbo].Customers

Plan B:. Create views to feed the data into your BI tools. Again, an easy process. Then your BI tool will just look at a single table.

Bruce

ACDC

It is easy, and will not break aware for you to manually create foreign keys.

I thought Aware would become dysfunctional with the presence of these keys. This is good news and seems an easy solution.

I wonder why Aware does not create these automatically, just to be more compatible with outside tools.

Thank you for this solution.

Anthony

PointsWell

ACDC wrote
I wonder why Aware does not create these automatically, just to be more compatible with outside tools.

Thank you for this solution.

Anthony

At a guess part of the reason is that it would be a very inconsistent implementation and hard to do in a bidirectional way.

The management of peer multiple relationships would be handled in SQL by an explicit intermediary tables but in AIM is handled by a solitary table per BO

For example

Pets have multiple People
People have multiple Pets

In SQL this would be handled by the following tables
Pets
People
PetPeople

If Pets also have multiple Vets and
Vets have multiple Pet clients then SQL would have another table
PetVetClient

AIM does not do this and instead (depending on where you create the peer relationships from) would create a second Pets_Ref table to manage the relationship with

pets_RID, pets_RMA, pets_REN

One of these is the ID in the relationship, one describes one side of the relationship (Pets) and the other describes the other side of the relationship (People or Vets). I cannot offhand remember which is RMA and which is REN.

If you created a foreign key onto this hidden table you'd get a situation where someone might think they can write via SQL to this table but would lack the filter of what type of BO. If you point a report writer at the table you'd also get a really confused view of relationships.

I am guessing that this is in part why BO.id is universally unique across AIM.

BLOMASKY

PointsWell wrote
ACDC wrote
I wonder why Aware does not create these automatically, just to be more compatible with outside tools.

Thank you for this solution.

Anthony

At a guess part of the reason is that it would be a very inconsistent implementation and hard to do in a bidirectional way.

The management of peer multiple relationships would be handled in SQL by an explicit intermediary tables but in AIM is handled by a solitary table per BO

For example

Pets have multiple People
People have multiple Pets

In SQL this would be handled by the following tables
Pets
People
PetPeople

If Pets also have multiple Vets and
Vets have multiple Pet clients then SQL would have another table
PetVetClient

AIM does not do this and instead (depending on where you create the peer relationships from) would create a second Pets_Ref table to manage the relationship with

pets_RID, pets_RMA, pets_REN

One of these is the ID in the relationship, one describes one side of the relationship (Pets) and the other describes the other side of the relationship (People or Vets). I cannot offhand remember which is RMA and which is REN.

If you created a foreign key onto this hidden table you'd get a situation where someone might think they can write via SQL to this table but would lack the filter of what type of BO. If you point a report writer at the table you'd also get a really confused view of relationships.

I am guessing that this is in part why BO.id is universally unique across AIM.

Hate to disagree, but in BOTH cases, there is an intermediate table. One called PetPeople other called Pets_Ref. Both are almost identical. I would "Assume" (and I know Assume means .....). Since Aware can use multiple backends. Each one defines and uses foreign keys differently. Aware does not need the FK so why should it create it.

Bruce

PointsWell

BLOMASKY wrote
Hate to disagree, but in BOTH cases, there is an intermediate table. One called PetPeople other called Pets_Ref. Both are almost identical.

It really depends...

If you create Pet first and then create Owner and Vet then create the m:n relationships to Pet then you will end up with Owner_Ref and Vet_Ref.

If you create Pet last and create the relationships to Vet and Owner from Pet you will end up with only Pet_Ref.

In the attached BSV I created the relationships all from Pet and there is only one REF table:
Screenshot 2023-10-24 at 14.44.19.png

Instances look like:
Screenshot 2023-10-24 at 14.49.13.png

I am not sure how you would create the fk on the RID column as it potentially relates to multiple other tables.

BSV in dropbox link as phpBB is generating an error when uploading zip files https://www.dropbox.com/s/y638hdetp203oia/peerTest.bsv.zip?dl=0

BLOMASKY wrote
Aware does not need the FK so why should it create it.

But I fully agree with this, they aren't used in AIM, so it would be an extra lift to build them for external use.

ACDC

If you create Pet first and then create Owner and Vet then create the m:n relationships to Pet then you will end up with Owner_Ref and Vet_Ref.

Would this method not be the correct method to setup the relationship then. This would get around one table pointing to multiple tables for the fk

This is a bit off-topic but.... Not sure which is the best way to setup relationships. Is it from the Bottom up or the Top down.
There are some circumstances where using IS DEFINED or IS UNDEFINED in a rule on a reference does not work, could this be related

PointsWell

ACDC wrote
If you create Pet first and then create Owner and Vet then create the m:n relationships to Pet then you will end up with Owner_Ref and Vet_Ref.

Would this method not be the correct method to setup the relationship then.

That would be a constraint and its too late to add constraints.

ACDC wrote
This is a bit off-topic but.... Not sure which is the best way to setup relationships. Is it from the Bottom up or the Top down.
There are some circumstances where using IS DEFINED or IS UNDEFINED in a rule on a reference does not work, could this be related

For 1:m relationships I always make the relationship from the BO that has a singular relationship, as this places the relationship into the main table for the BO. If you put it on the BO that can have multiples then you can end up with BO_REF tables.

There was a long thread on it a while ago.

The IS UNDEFINED limitation is outlined here

Though I believe there is another issue that is undocumented.

ACDC

The IS UNDEFINED limitation is outlined here

Though I believe there is another issue that is undocumented.
Top

I am not sure if it is the same issue, if it is, then it should be described better with other examples. If you do not know about this the app becomes dysfunctional and takes a while to realize where the fault is

Example:
If there is a reference that is Owned By eg InvoiceItems OwnedBy Invoice (singular relationship) and I have a rule on the InvoiceItem object: If InvoiceItem.Invoice is DEFINED then ....do something, It does not work, it cannot establish whether it is Defined or Not.

So any logic that I want in place to reference the InvoiceItem.Invoice, the only way to establish if it is DEFINED is to use COUNT. (extra overhead ?)
However if I change the relationship to a PEER relationship the problem goes away

Jaymer

Use InvoiceItem.Invoice.ID > 0

ACDC

Use InvoiceItem.Invoice.ID > 0

This seems to be a solution and more efficient than COUNT I suppose?
Is this the preferred method then, albeit very different thinking?