Table of Contents
Shapes representing a table, view or query (if you have Query Xtractor or Data Xtractor) show their data columns as items. Column items may get a primary and/or foreign key icon, a data type and a default value. When the data type name gets a star (*) suffix, the data value in that field is optional (i.e. it can be NULL).
Golden keys represent primary keys, grayed keys are foreign keys, half-half are both primary and foreign keys.
When your model diagram is set to Show Categories, all shape items are grouped under specific collapsible categories, such as Relationships, Keys and Columns. When collapsed, the whole section is hidden when the shape becomes unselected:
Built-In Relationship Items
For each PK-FK (primary keys –> foreign keys) relationships, we automatically generate, on each metadata import or synchronization, surrogate built-in relationship items. They originally have the name of the other table they connect with (a suffix may be appended to ensure the name is unique).
These special items make it easy to discover, expand and navigate through relationships. Just drag such an item and drop it outside its shape, and it will expand into a connector between two tables. Each such item has a reverse item, so the relationship can be discovered and expanded both ways. When one item is expanded, the reverse item is automatically expanded as well. When the connector is removed, both items reappear in their original shapes.
The easiest way to determine which particular keys, shapes and connectors are involved into a specific relationship, is to look at the highlighted parts while moving the mouse over an element. In following diagram, the last items in each shape, dbo.Album and dbo.Artist, are such relationship items that implement the actual PK-FK relationship on the ArtistId keys. On the right, you get a similar highlight after you expanded the relationship into a connector.
Custom Relationship Items
You can extend your model creating similar custom relationships, for other than the built-in PK-FK associations. For instance, a database may come with no foreign keys define, but the relationships are clearly there. Or useful relationships – such as FirstName+LastName as person names in several tables – have no referential integrity at the database level.
To create a custom relationship, drag one column item over another related item. If columns have similar types and such a relationship can be defined, you may continue to add other column pairs to this new join or not. Custom relationships are not saved in the remote database and can be deleted.
Chain Relationship Items
A chain relationship is a particular link of two or more continuous built-in or custom relationships.
Column names may get a column alias. This is a different display name you may provide just within Xtractor (nothing is changed in the database and the original database names continue to be used internally in the SQL generated queries). They can also be highlighted (i.e. displayed on a yellow background) with a double-click. Or hidden (i.e. displayed straight through when shape selected, made invisible after that):
Items can be moved within their shape by drag and drop. Multiple selection operations are available through the contextual menu. When a table column is also a single foreign key in a relationship, a lookup field can be eventually set for it.
Relationship items show their cardinality (to-many, to-one or to zero/one) as an icon. A dashed line indicates relationship is optional (i.e. to-zero is possible).
With a table or view selected, look in the Objects browser for more information about its column and relationship items: