Relationships

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.

Control relationship lines

Relationship lines are customizable in SqlDBM diagrams. Users can bend relationship lines at any point and set their shape permanently on the diagram. This functionality provides complete control over diagram design and ensures that relationships are properly situated for clearer separation and understanding.

A demonstration of how to bend a relationship line around a fixed point is included in the video in the section below.

Physical relationships

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.

Recursive relationship

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. See the video above for a demonstration of how to create and edit the associated columns.

Virtual relationships

For tables

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.

RELY property (Snowflake)

Snowflake constraints (PK, FK, AK) now support RELY in Reverse/Forward Engineering and object properties. The RELY property is a keyword included in the constraint definition and acts as a hint for the Snowflake optimizer to rely on the integrity of the constraint and perform join elimination on views and redundant queries.

Set or unset RELY by clicking on any constraint from the Keys section of the table properties and selecting the RELY option.

For FKs, clicking on the relationship line will bring up this menu directly.

An article describing how RELY can improve query cost and performance is available here.