Aware's Created DB Tables have no Foreign keys - why?

BenHayat

Hi guys;

When I develop application in other tools using MSSQL or MySQL, we used "Foreign Keys" to create relationships among the tables. So regardless of what tools was used to build the app., other tools could easily connect to tables and use the relationships for query and building charts.

When I look at the tables that Aware has created in MySQL, there are no Foreign key created. I tried to create a relational diagram, and every table showed as independent.
I posted a new thread regarding Aware and BI and if I were to use a third party to read Database and produce reports, how would we use these tools when there is no relationships established at the DB Level? Seems like all the relationships are built within Aware.
How do you guys overcome this situation?

Thanks!

ACDC

Have you looked at the _RID _REM and _RMA attributes in the table and the XXXX_ref table referenced with each table e.g. quote and quote_ref tables

BenHayat

ACDC wrote
Have you looked at the _RID _REM and _RMA attributes in the table and the XXXX_ref table referenced with each table e.g. quote and quote_ref tables

Yes, I have... But as an Aware developer I'm not concern about it, however there will be another team who will use other tools to connect to these tables, those tools automatically use the "Foreign Keys" to see the relationships. Just like the diagram tool saw all the tables as flat tables and couldn't establish relationships.

My question is/was how do we deal with this situation when other team need to connect to out DB and do query using SQL query and etc.?

Thanks!

intra

Maybe you need to create the view for them.

I am assuming you intend to make up a star schema to help with OLAP cubes?

Peter.

BenHayat

intra wrote
Maybe you need to create the view for them.

I am assuming you intend to make up a star schema to help with OLAP cubes?

Peter.

At first they had asked to provide a "graphical/diagram" schema of the tables. Many clients when we design system for them, we always use MSFT's SQL Server Management Studio to create the diagram. This is their first request, before even getting to providing them either views or access to the tables to create SQL query. But the fact that foreign keys are not there concerns me how to respond to this team.
And that's why I bring this up, how do you share your DB with other tools to access your data, i.e. BI or charting and analysis?

Thanks!

kenlyle

It appears that this issue is still open in the v7 Beta, looking at the SQL Server database created by the CRM sample app.

I agree with Ben that it would be nice to be more compatible with the database diagramming tools, code generators, report writers, etc. that use the FK relationships in the database.

The converse is also true. Sometimes, one would like to "re-skin" an existing application, and it would be nice to be able to just take over the existing schema, so that the existing code continues to work.

In case it helps anyone, here's a SQL Server script for identifying FK references to a table:

SELECT
OBJECT_NAME(f.parent_object_id) TableName,
COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName
FROM
sys.foreign_keys AS f
INNER JOIN
sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN
sys.tables t
ON t.OBJECT_ID = fc.referenced_object_id
WHERE
OBJECT_NAME (f.referenced_object_id) = 'YourTableName'

BenHayat

kenlyle wrote
It appears that this issue is still open in the v7 Beta,

Yes, this subject is still open. Aware creates it's own way of data relationships by not using FK indexes, which break the rules of being able to share DB with other tools that use standard FK to establish relationships.

I had tried to use diagram tools to show my data structure to prospects and each table showed as a flat table on diagram. Some reporting tools also use FK to find other related tables automatically and they were not able to.

kenlyle

Now that v7 has been released, does anyone have more info on this question/issue, particularly around making existing tables/schema first-class citizens in AwareIM?

customaware

The paradigm is thankfully the same in V7 as in V6 in respect to DB Table structure.

If you are waiting for Aware IM to dump it's technological advantage to revert to having the Developer manage Primary and Foreign Keys as they need to do in less advanced environments then you will be waiting a long time.

The reason we love it so much is because it is the way it is.

kenlyle

Thanks, Mark.

Our schema is over 500 tables, and just over 200 of those are Admin related, like lookups, field layouts, etc.

I am assuming that Aware is using some kind of ORM, given that Java is OO, to manage relational data. It seems like maybe there could be a setting of some kind in the ORM that specifies using the existing keys to build the relationships, allowing us to continue running the existing application while adding cool and attractive new functionality in front of the same schema.

BLOMASKY

My 0.02 Cents worth

A foreign key accomplishes 3 things

1) It enforces referential integrity (which Aware does a mighty fine job)
2) Speeds up Joins (since it creates a SQL index) which SQL usually does on its own if needed
3) give "hints" to programs about joining tables.

It is easy to manually create the foreign keys for #3. (of course, that assumes you are NOT planning on changing the relationships in Aware

Bruce

johntalbott

eagles9999 wrote
The paradigm is thankfully the same in V7 as in V6 in respect to DB Table structure.

If you are waiting for Aware IM to dump it's technological advantage to revert to having the Developer manage Primary and Foreign Keys as they need to do in less advanced environments then you will be waiting a long time.

The reason we love it so much is because it is the way it is.

It's nice in the respect that it's easy to develop an AwareIM app ... if it's the only tool that will ever come into play.

In general, the application layer managing data integrity at the database level falls apart quickly.

How would sell this to a typical enterprise where there are a number of systems accessing a given database?

customaware

If it doesn't satisfy your needs then there is a pretty simple solution.

johntalbott

eagles9999 wrote
If it doesn't satisfy your needs then there is a pretty simple solution.

I expected you'd offer a more productive suggestion/comment.

Do you really think that the people have brought this topic up are misguided?

Do you feel that this topic is not worth discussing? If so, there is a pretty simple solution.

tford

It may not be practical for all external DB tables accessed by AwareIM, but I have an application that accesses a Kentico CMS database as an "existing external" table, then creates certain AwareIM cross reference records in an "AwareIM automatic" table. The AwareIM table has plenty of cross references to the existing external table which allows me to take full advantage of all AwareIM features in the AwareIM automatic table. It's really the best of both worlds.

aware_support

We do intend to improve communication with external databases in the near future. In fact, it will be one of the major directions for the next version.

The improvements will include the foreign key constraint and better automatic recognition of relationships in external databases.

johntalbott

aware_support wrote
We do intend to improve communication with external databases in the near future. In fact, it will be one of the major directions for the next version.

The improvements will include the foreign key constraint and better automatic recognition of relationships in external databases.

Awesome .. this is music to my ears!

BenHayat

aware_support wrote
We do intend to improve communication with external databases in the near future. In fact, it will be one of the major directions for the next version.

The improvements will include the foreign key constraint and better automatic recognition of relationships in external databases.

As the OP for this thread, I'm super happy to hear that Support. This has been the # 1 concern when I demo Aware to prospects who would like to use Aware in conjunction with other products, i.e. third party Business Intelligent software to analyze data. These software require foreign keys and relationships.

There have been two cases that I've ran into:
Case 1 is that Aware is the creator and owner of the tables and other third party want to access the DB like a any other DB and has the full schema about tables, relationships and using foreign keys for performance and etc. This relates mostly to Aware developers.

Case 2 is where I've ran into, the most. With the powerful ORM and fast application development in AwareIM, it begs to introduce Aware to companies who can use to read/write data to an established application. For example, Aware can step in, automatically read the schema and create BO structure and Aware developer can then build Mobile apps. In this case, Aware will/should not be able to change the structure of the DB, since the DB is maintained by DB Admin. Any changes made to DB, Aware should recognize it and make adjustments to it's internal BO structure.

I would say 80% of the cases that I've demoed Aware to, has been case #2. As a Consultant, I want to offer Aware but they ask for DB features that are not in Aware and I can't take the job.
I hope this feedback was helpful.

crisg0112

aware_support wrote
We do intend to improve communication with external databases in the near future. In fact, it will be one of the major directions for the next version.

The improvements will include the foreign key constraint and better automatic recognition of relationships in external databases.

Support,

This is GOOD NEWS no .. AWESOME NEWS!

BLOMASKY

Ben, you said:
Aware will/should not be able to change the structure of the DB, since the DB is maintained by DB Admin. Any changes made to DB, Aware should recognize it and make adjustments to it's internal BO structure.

Aware adds 3 columns to every table to store the relationships. Why would anyone else care if you add extra columns? It will not 'break' any other application. I am not sure how any other application could do what aware does just by interpreting foreign keys. All that can do is enforce referential integrity and doesn't help when you, in aware, type ... WHERE (X IN Y.Z)

Bruce