The auto-suggest relationships feature allows users to quickly add relationships to projects where physical constraints may not have been defined for some or all of the tables.

Suggested Relationships creates virtual objects which live as metadata inside the project. Virtual Primary Keys (VPK) and Virtual Foreign Keys (VPK) (as well as corresponding virtual relationships) are generated as a result. These virtual constraints allow users to visualize relationships (through existing virtual relationship functionality) without creating physical constraints and forward engineer changes to the database.

Suggested Relationships is a three-step process that can find and suggest (virtual) primary and foreign keys and create these relationships from a guided wizard. The steps include:

Specify the naming pattern for key fields
Suggest VPKs based on the naming pattern
Suggest VFKs based on existing VPKs

Here is a simple example of Suggested Relationships in action:

Access Suggested Relationships

The Suggested Relationship wizard can be found in the main SqlDBM menu and enabled as part of Reverse Engineering.

Suggested Relationships from the menu

Open the main SqlDBM menu and select "Suggested Relationships" to open the wizard.

Suggested Relationships from Reverse Engineering

While performing Reverse Engineering, select the "Suggest relationships" checkbox to open the wizard after pressing "Import" and completing the RE operation.

Suggested Relationships

The Suggested Relationship wizard guides the user through a three-step process. From defining primary key naming parameters, suggesting VPKs, and finally, suggesting VFKs.

Once PK naming patterns have been set, and some PKs (virtual or physical) exist, the wizard can be run on-demand in any order.

Setting the naming patterns

Naming Patterns determine which columns will be considered as candidates for VPKs. SqlDBM will search for columns containing the keywords in the "Include" tab while ignoring the columns containing keywords from the "Exclude" tab.

Note that common keywords are provided by default.

Keywords will be saved in the project for subsequent use.

Suggest Virtual Primary Keys (VPK)

Generate

Once "Patterns" have been set, click the "Find Virtual PK" button or the triangle icon to generate suggestions.

Review

Once suggestions are generated, users are free to review and modify them.

Suggestions for individual tables can be turned on or off individually or in bulk using the corresponding checkboxes next to the table name on the left.

VPK column suggestions can also be modified for each table by clicking on the pencil icon to the right of the corresponding column proposal.

Regenerate

If changes are made to the underlying objects, press the triangle icon to regenerate the suggestions.

Create

Once the VPK suggestions have been reviewed and configured according to business rules, press the "Create [n] VPK" button to finish generating the virtual PKs.

The resulting VPKs will be displayed on the diagram on corresponding columns, similar to physical constraints.

Note, unlike physical PKs, VPK columns will not shift to the top of the table as the intention is not to alter the physical structure.

Suggest Virtual Foreign Keys (VFK)

Virtual Foreign Keys and corresponding Virtual Relationships can be suggested once project tables contain some PKs (physical or virtual). This feature will look for occurrences of PK or VPK columns in other tables and suggest the creation of VFK relationships.

Set Options

The following options exist to fine-tune the VFK suggestions. Remember that the operation can be run many times with different options selected. Select "Set Options" in the "Suggested Relationships" tab to bring up the options.

Strict matching - (enabled by default) suggestions only consider original parent dimensions (whose PK is not also an FK) and avoid proposals between fact tables.
Parent name match - Used when PK column naming follows a generic pattern in dimension tables and fully qualified naming when used in conformed dimensions. For example, the PK column is named "ID" in the CUSTOMER table and "CUSTOMER_ID" in the ORDERS table.
Data type match - Suggested columns must match in name AND data type.

Generate

On the "Suggested Relationships" tab, click the "Find Virtual Relationship" button or the triangle icon to propose relationships.

Review

Review the suggested relationships and select or unselect proposals using the corresponding checkboxes to the left of the tables.

For identifying relationships, parent and child table order can be swapped using the divergent arrows icon on each suggestion.

Create

Once the VFK suggestions have been reviewed and configured according to business rules, press the "Create [n] Relationships" button to finish generating the virtual FKs.

Underlying logic

Suggested Relationships operates on the following logical settings:

VPK suggestions must contain "Include" keywords AND not contain "Exclude" keywords.
Keywords are NOT case sensitive.
VPK suggestions are only offered for tables WITHOUT existing PKs (physical or virtual)