Relationship(Join Column identification) without foreign Key or Virtual Relations
nssidhu74 3 years ago • updated by hannah gryska 1 year ago • 8
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
Customer support service by UserEcho
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.
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
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.
Glad you mentioned that, as Virtual relationship is something our team has started handling and we plan to release this feature by next month.
Support Team - SqlDBM
This Virtual Foreign Key is all I can make currently. How do I get an ordinary FK? This is frustrating.
Hello, sorry you are feeling frustrated there. When you click on this icon, attached below, you should be able to choose which type of relationship you'd like this to be. Here's an article about Foreign Keys, and also one on relationship types which may help you.
Pleased to announce that we have extended support for Virtual Relationships, now users can :
From top-center menu, pick type of relationship line (system remembers your selection for next time) :
Access properties of relationship line:
Please check our this new feature, hope this helps your productivity.