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:
- by pressing the shortcut Ctrl+R
- with a click of the toolbar icon
- or with a right-click anywhere in the central pane
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:
|zero or one|
|zero or more|
|one 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.
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:
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