Relationship Items

On metadata import, we automatically generate paired surrogate relationship items for any built-in PK-FK relationship. We’ll have one relationship item per any PK-FK combination, and another reverse item on the other side of the same relationship. We also generate similar relationship items for custom joins and chain relationships.

Table of Contents

Shape Relationship Items

Look at the left table below. It has one PK (user_id) and one reflexive FK (reports_to). How can you tell what other relationships users can be in, if you can determine this only by looking at FKs? And Users is a pretty independent table, its main relationships with other tables consist in propagation of its PK as FK in those tables. Also, there could be composite PKs and FKs, with more than one key field. Relationship items group together all these fields.

relationship-items1

Look at the second Users table now. Hard to believe this is in fact the same table, it’s just that we expanded its Relationships category and unveiled its generated relationship items. Without looking everywhere now, we can say easily this table is in relationship with Address, Managers, Clerk and many other tables.

Expanding Relationship Items

By simply dragging some of these items and dropping them outside the shape, we generated a much better view, like the one below:

relationship-items2

Mouseover Highlights

Whenever you move the mouse over a visual diagram element – shape header, shape item or connector link – the element itself and most if not all other related elements will eventually be highlighted, i.e. displayed with blue bold lines, borders and/or backgrounds.

The Mouseover Highlights feature works when:

  1. Show Mouseover Highlights toolbar menu item is checked. This is a global option, by default set. The switch will turn the feature ON or OFF for all diagrams in the application.
  2. No diagram element should be selected. You may click on an empty diagram spot to accomplish this.
  3. No comment, shape header or item edit mode.
  4. Mouseover highlights will not appear on generated or exported static diagram images.

Mouseover Shape Highlights

Moving the mouse over a shape header, with no item selected or highlighted, will usually highlight the shape itself (its borders) and all its in and out connectors, including the recursive connectors:

This applies to all sorts of expanded, collapsed, minimized or transparent shapes. Same diagram with all shapes collapsed:

Mouseover Item Highlights

Move your mouse now over the items area of the expanded User shape, and stop over the user_id primary key (PK) item. Besides the item itself, all its related foreign key (FK) items from the same shape (for a recursive relationship) or from other expanded diagram shapes will become highlighted:

Here is the generic use case, when the mouse goes over a table/view-based shape column item. Regular non-key column-based items usually have no related elements, so they will be highlighted alone. But both primary key and foreign key based items will highlight the related PK or FK from the other table (or the same table when recursive relationship) and the relationship connector. Same elements are highlighted when you move over the connector, instead of its PK/FK items:

Mouseover Connector Highlights

Connectors reflect PK/FK-based relationships. They are always associated with one or more pairs of PK+FK-based shape items. We also automatically generate relationship items, used to expand the relationships into connector links when dragged outside.

When you move your mouse over such an expanded connector, all its related elements – the shapes at both ends, and its related PK/FK-based shape items – will appear highlighted as well. Here are the two typical use cases, with a regular simple one-to-many relationship between two tables (one province has zero or more user addresses), and one recursive/reflexive relationship (for the parent user):

When you select and Remove each of these expanded connectors, they will collapse into their relationship items, visible now in the related shapes. Moving the mouse over these relationship items will highlight the same PK/FK-based shape items, as they encapsulate and reflect the same relationships:

Mouseover Chain Highlights

Chains are specific Model Xtractor sequences of continuous relationships grouped together. For two A -> B and B -> C relationships, A -> B -> C can be defined as a chain. We intensively and transparently use this techniques to generate many-to-many chains over the intersection tables, and hide these last tables by default from the diagrams. This may drastically reduce clutter and help translate a physical implementation detail into a more simple and appropriate logical abstraction.

We have such a generated many-to-many chain relationship between roles and privileges. When you move your mouse over this connector, some additional chain-related relationship shape items may be highlighted as well. Besides the connector itself, its shapes at both ends, and its PK/FK related items, we have some PrivilegeToRole relationship items highlighted as well. Drag and drop any of these last two items outside their shapes, and you will see the intersection table bypassed by the our chain. Remark all these relationships may be displayed on the same diagram, even when some are derived and redundant. The red lines show the PK-to-FK propagation for the two one-to-many relationships the chain was based upon:

Recursive/reflexive chains – bypassing an intersection table connected to the same other table at both ends – are similar. Each user can work with zero, one or more different other users. The highlighted relationship connector from the left is a many-to-many recursive chain. It is based on the user_id PK of the User table, and exposes two additional collapsed relationship items, that can be expanded to display the hidden WorksWith intersection table. As an alternative, you can drag and drop this new table on the diagram. With auto-expand ON, it’s likely you will get those last items expanded into two one-to-many connectors to the new added shape:

If you rather select and remove the chain connector instead, you’ll see two new collapsed relationship items added to the User shape. Their generated names starts with the hidden intersection table name, followed by the table name at each end (in this case User), separated by a dot. The dot within a relationship item name tells you that is a chain.

Finally, go back and look closer to the expanded relationships between WorksWith and User. They are both based on the propagation of the same user_id PK from User as different FK in WorksWith: move over each connector and a different FK item is highlighted. Move your mouse over the FK items instead, and they will basically highlight the same visual relationship elements: