Relationship links in ER diagram

There are several ways to create a new relationship between two collections in the ER diagram of a model:

- by drag-and-drop action in the ERD by selecting a child field and dragging while holding the mouse button towards a parent field with a compatible field type in another collection

- via the Actions menu:

Menu - Add relationship

- by pressing the shortcut Ctrl+R

- with a click of the toolbar icon Image 

- or with a right-click anywhere in the central pane 

ER Diagram - contextual menu - add relationsh


and choose 'Add Relationship'.  


You then proceed to fill relationship properties.


Hackolade documents and helps visualize 2 types of relationships: foreign key and foreign master.  The foreign key relationship is the unique identifier of the data.  The foreign master relationship identifies the master for the duplicated field of the denormalized data. 


Hackolade adopts an IE-like notation (Information Engineering notation) applied to physical models in order to display multiplicities on each side of the relationship to represent the cardinality: 


Relationship cardinality - oneonly one
 Relationship cardinality - manymany
Relationship cardinality - zero-to-onezero or one
Relationship cardinality - zero-to-manyzero or more
Relationship cardinality - one-to-manyone or more


Typically, a relationship identifies a parent field within a parent collection on one side, and a child field within a child collection.  Below is a simplified example... Say we have a master collection of customers, each identified by a unique id.  When creating a document for each sales order, the customer name and address are repeated so as to avoid having to perform joins each time an order is accessed.  


Relationships and denormalization

Three relationships can be documented in the model:

  • a foreign key relationship for the customerID in the customers collection
  • a foreign master relationship for the customerName
  • a foreign master relationship for the customer address


Occasionally, it might be needed to model recursive relationships where the parent and child collections are identical.  In effect they are siblings:

Relationships - siblings


The ERD entity boxes have been enriched with visual indicators.  Here is a list of abbreviations:

fk: foreign key

fm: foreign master

dk: denormalized key (parent of a foreign key.)  Marks keys that are denormalized elsewhere in the model.

dm: denormalized master (parent of a foreign master.)  Marks non-key attributes that are denormalized elsewhere in the model.  


Both dk and dm attributes allow access to the Where-Used function in the contextual menu to find all the places in the model where they are denormalized.


In RDBMS plugins, it is possible to represent composite (or compound) foreign key relationships 


Relationships - composite foreign keys