+5
Accepted

Relationship(Join Column identification) without foreign Key or Virtual Relations

nssidhu74 8 months ago updated by Support 2 weeks ago 5

Right now to show relation between column, we have to create Primary Key and than it automatically create Foreign Key in the related table.

This is good for OLTP Table Design. 

For Big Data Workloads that don't support Enforced Relationship, but only virtual relation the above restriction are too strict.

Also the names of column can be different in both the tables.

Please relax the current criteria for Relationship or allow creation of Virtual Relationship between tables with multiple columns involved


+2

I've encountered the same problem in a different scenario. I'm modelling a source app database that doesn't use the primary key of the 'one' side in the implementation of their relationships - I'm attempting to show the join logic in the model but keep on being forced to use a primary key on the one side and a foreign key on the many side. That should be the default but then with the option of changing the columns used in the relationship - perhaps a different relationship type is needed.


I'm busy doing an evaluation of SqlDBM versus Er/Studio Data Architect which is up for renewal soon - this limitation could be a deal breaker. 

+1

With the same requirement, my usage is that the physical layer does not create strict foreign keys, but use join for queries, and hoping to see the relationships of each table in the model view.


I would appreciate your support.

You can actually change the column in the destination table when the key has been created; select the relationship and then you can alter the name in 'Rolename' section

ErRelationship


As can be seen in the diagram, first thing in big Data world there is not concept of Foreign Keys(they exists as non enforced).
Next you can see join between tables does not include all the columns(Primary or Indexed).
It becomes very hard to create diagram in such scenarios.
May be you can come up with concept of Virtual Relationship which would never be created in physical layer.
Current implementation is too restrictive and takes out large amount of audience from using your product. As you can see i have used another competing product because your product does not support such scenarios.

Accepted

Glad you mentioned that, as Virtual relationship is something our team has started handling and we plan to release this feature by next month.

Thanks,

Support Team - SqlDBM