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?
Propietry Database Design change utility
Re: Propietry Database Design change utility
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]([ID])
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
Something like this:
ALTER TABLE [dbo].[Orders]
ADD CONSTRAINT [fk_OrdersToCustomers]
FOREIGN KEY([ob_Customers_RID])
REFERENCES [dbo].[Customers]([ID])
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
Re: Propietry Database Design change utility
I thought Aware would become dysfunctional with the presence of these keys. This is good news and seems an easy solution.It is easy, and will not break aware for you to manually create foreign keys.
I wonder why Aware does not create these automatically, just to be more compatible with outside tools.
Thank you for this solution.
Anthony
-
- Posts: 1460
- Joined: Tue Jan 24, 2017 5:51 am
- Location: 'Stralya
Re: Propietry Database Design change utility
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.
Re: Propietry Database Design change utility
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.PointsWell wrote: ↑Mon Oct 16, 2023 10:11 pmAt 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.
Bruce
-
- Posts: 1460
- Joined: Tue Jan 24, 2017 5:51 am
- Location: 'Stralya
Re: Propietry Database Design change utility
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: Instances look like: 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/y638hdetp203o ... v.zip?dl=0
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.
Re: Propietry Database Design change utility
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 fkIf 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.
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
-
- Posts: 1460
- Joined: Tue Jan 24, 2017 5:51 am
- Location: 'Stralya
Re: Propietry Database Design change utility
That would be a constraint and its too late to add constraints.
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.
Re: Propietry Database Design change utility
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 isThe IS UNDEFINED limitation is outlined here
Though I believe there is another issue that is undocumented.
Top
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
Re: Propietry Database Design change utility
Use InvoiceItem.Invoice.ID > 0
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
Jaymer
Aware Programming & Consulting - Tampa FL
Re: Propietry Database Design change utility
This seems to be a solution and more efficient than COUNT I suppose?Use InvoiceItem.Invoice.ID > 0
Is this the preferred method then, albeit very different thinking?