Relationship lines in ER diagram

Relationship lines in Entity Relationship diagrams are useful to understand the relationship in the data, and to define Foreign Key constraints used in query joins, and to ensure data consistency.  

Note that in NoSQL databases FK relationships are not declared to the database instance and hence not enforced.  They are nevertheless useful to understand relationships in the data as well as denormalization when data is repeated in different places.

 

Note also that in analytics databases, primary key and foreign key constraints may be absent for performance purposes.  If you have reverse-engineered in OLAP store and your model does not show any PK and FK relationship lines, it may still be possible to discover them using our Infer PKs & FKs feature.

Create a Foreign Key relationship line

There are several ways to create a new relationship line between the attributes of two entity boxes in the ER diagram of a model:

• with a drag-and-drop action in the ERD by selecting a child attribute and dragging while holding the mouse button towards a parent attribute with a compatible attribute type in another entity.  If the parent attribute is not yet a PK for its table, the application automatically makes the parent attribute a PK, if there is no conflict (i.e. if another attribute in the same entity is not already the PK.)
   

• since v7.0.4, the drag-and-drop behavior can be modified from its default via Tools > Options > General to become "from parent to child".  This is particularly useful in dimensional modeling when you want to take the PK of a parent dimension table and automatically create:

  • the same attribute in the child fact table with the same data type.  Note that you may modify the name of the attribute in the fact table.
  • the FK relationship from the child attribute in the fact table to the parent PK attribute in the dimension table.

 

• via the Actions menu:

• by pressing the shortcut Ctrl+R and filling the properties in the right Pane
• with a click of the toolbar icon  
• with a right-click anywhere in the central pane to display the contextual menu

 

and choose 'Add Relationship'.  

• from the Properties Pane of a child attribute

 

 

Relationship lines

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: 

 

Symbol	Description
	only one
	many
	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: destination or denormalized key (parent of a foreign key.)  Marks keys that are the parent of a foreign key or are denormalized elsewhere in the model.

dm: destination or denormalized master (parent of a foreign master.)  Marks non-key attributes that are the parent of a foreign attribute or 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.

 

Composite relationships

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

 

 

Adjust relationship lines

With large and complex relational models, the automatic positioning of foreign key relationship lines might look insufficiently organized.  As a user, you might want to adjust the lines differently.

 

Automatic or manual adjustments

You may move entity boxes around by dragging the box title bar, in which case the existing lines follow and auto-adjust.  Or you may choose to adjust lines separately, as described below.

 

 

Line geometries

In ERDs (hence excluding here graph diagrams, where edges tend to be curved), there are 2 geometries: orthogonal or oblique.

 

An orthogonal line is one where the segments can only be vertical or horizontal. As a result, the angle between the line and the entity boxes can only be 90 degrees. An orthogonal line can be straight if it has a single segment. If multi-segment, the segments can only have a 90-degree angled elbow between them.

 

An oblique line must have a single segment and be straight. The angle between the line and an entity box can be anything, including 90 degrees in some cases.  Oblique lines are often used in star schemas, but not only.

 

Line anchors and segments

When you select a line, it gets highlighted, and nodes appear at each end, plus in middle segments, if any.  Nodes are are used to move the anchors and segments, by click-and-drag.  The mouse cursor when clicking indicates the possible direction(s) you may drag the node.

 

Each line has 2 anchors, one on each end and represented by a node, attached to their respective entity box.  Middle segments, i.e. segment other than the ones at the end of the line, each have a node in the middle of the segment.

 

 

A line may have one or more segments, depending on the relative positions of the entity boxes and the line anchors.  We don't advise this, but it is possible for a line to have as many as 7 segments.

 

The number of segments is automatically adapted when you move anchors to a different side of its entity box.

 

Movements

You may move:

• the whole line, whether orthogonal or oblique,
• each anchor,
• or each individual segment of a line.

 

To move a line, just grab anywhere in the line, and move in any direction.  Depending on the direction you drag, segments and anchors are moved, anchors may change box sides,  and the number of segments are automatically recaculated.

 

To move a single segment at a time, you may grab the node in the middle of the segment, or the anchor if it connects to an entity box. As you click on the node, the mouse cursor turns into a 2-headed arrow indicating the possible directions that you can drag.

 

As segments get extended past the anchor of the line, the number of segments may be recalculated, and the anchor moved accordingly.  The application will not allow a line to be hidden by any part of an entity box to which it is attached.

 

Similarly, the number of segments can be reduced by aligning parallel segments, or by moving an anchor to a box side closer to the opposite andchor.

 

Since an oblique line can only have a single segment, the anchors on each end can only be attached to the one or maximum 2 sides of the entity box that are facing the other anchor.  

 

Each anchor can be moved, but is restricted to the one or maximum 2 sides of the entity box, so as to avoid having the line covered by any part of the entity box.

 

At any time, you may swicth geometry, which will restore the line to default anchors