Documentation

No results
    gitHub

    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:

    Menu - Add relationship

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

    ER Diagram - contextual menu - add relationsh

     

    and choose 'Add Relationship'.  

    • from the Properties Pane of a child attribute

    Properties - Foreign Key relationship

     

     

    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: 

     

    SymbolDescription
    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: 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 

     

    Relationships - composite foreign keys

     

    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.

     

    Image

     

    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.

    ER lines - orthogonal

     

    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.

    ER lines - oblique

     

    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.

     

    ER lines - 3 segments

     

    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.

    ER lines - max 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.

    ER lines - moving entire line

     

    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.

    ER lines - 3 segments movements

     

    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.

    ER lines - 4 segments movements

     

    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.  

    ER lines - oblique movements

     

    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.

    Image

     

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

    ER lines - style and gemetries