A relationship between two database objects can be physical (expressed through DDL) or implicit. SqlDBM allows users to diagram any kind of relationship as well as generate the related DDL where applicable.
A physical relationship occurs when the primary key of one table is used as a foreign key of another. A foreign key constraint can then be created to establish the physical relationship.
Create a relationship between two tables by dragging the bottom right connector from the source to the target table. The primary keys of the source table will automatically be added to the target as foreign keys.
Note that any existing foreign key constraints will automatically be recognized and represented by SqlDBM through the "Reverse Engineer" feature.
Some tables can have an implicit relationship to themselves. For example, an employees table where both employee and manager IDs and relationships are stored. While there is no corresponding DDL to document this relationship, you can still show it on the project diagram.
To create a recursive relationship, drag the connector back to the table itself.
Note, as no fields can be specified in a recursive relationship, it may be useful to add a description on the relationship object itself.
SqlDBM is designed to automatically detect logical relationships (PK, FK) through table constraints. However, as Snowflake and other Data Warehouses do not enforce these types of constraints, some users omit to define them. Virtual Relationships enable users to define and visualize their table relationships within the SqlDBM tool without having to declare PK/FK constraints directly in the database.
Create Virtual Relationship lines between tables (which will not affect DDLs like create FK) from the top-center menu. Select Virtual Identifying or Non-Identifying type of relationship ( SqlDBM remembers your selection for future use). Connect the source and target table just as with physical relationships.
Even after a virtual relationship is created, you can change Identifying or Non-Identifying status by selecting the relationship line and clicking the "Identifying" checkbox.
To identify the columns which form your virtual foreign key, select the relationship and click the columns of the Virtual Relationship for both parent and child tables from the right panel menu.
To distinguish Physical from Virtual relationships on the diagram, virtual relationships are colored. Identifying virtual relationships are colored green, and non-identifying, are orange by default. The color can be changed in the color properties in the right panel menu of the relationship.
For extra context, you can also provide a description of the virtual relationship in the right panel menu.
For views, functions, and procedures (Enterprise)
Views, Functions, and Procedures can be connected/related to other objects on a diagram using virtual relationships. Available in all Enterprise plans.
To do this, select a virtual relationship type from the top menu of the diagram, then click and drag from a source object to the target object.
Virtual connections can be made between any objects on the diagram (views, tables, functions, or procedures).
Relationship Role Names
By default, when a foreign key relationship is created between two tables, the primary key(s) of the parent table are included directly as foreign keys in the child. If it is required that the column/s be named differently in the child table, a role mapping can be used.
Click on the relationship to access its options in the right-hand menu. Click on the "Rolename" dropdown.
Enter the desired name of the foreign key in the child table next to the corresponding parent column.