Containment Relationships

Go Up to Creating and Editing Relationships

A containment relationship is used in physical models that support hierarchies such as JSON, MongoDB, Google BigQuery, Databricks, and now traditional platforms that allow embedded JSON. These structures include nested objects which can be contained in top level objects such as tables or collections or other nested objects. Nested objects and containment relationships will be created

• Manually within physical models
• During the reverse engineering of such hierarchical platforms
• By the user during generation of physical objects from logical models that have relationships with the "Containment" flag set

Manually Creating Containment Relationships in Physical Models

To create a new containment relationship

1. On the Data Model Explorer, click the Data Model tab.
2. Choose Insert > Relationships > Containment Relationship, this changes the cursor icon and allow you to select the objects in the main window with which to create the relationship.
3. Click on the object containing the other object.
4. Click on the object contained within the previously clicked object.

Double clicking on the relationship line will open the Containment Relationship Editor where you can edit settings

The 1 indicates a single nested object; * indicates an array.

During the reverse engineering of such hierarchical platforms

When you reverse engineer a physical platform that natively supports hierarchical structures such as JSON, MongoDB, Google BigQuery, Databricks, Data Architect will create nested objects linked via containment relationships.

For the following platforms that support embedded JSON:

• MS SQL Server 2019 & 2022
• Azure SQL DB
• Azure Synapse Analytics
• Oracle 19c and Oracle 23ai
• Snowflake
• PostgreSQL

there is an option in the Reverse Engineer Wizard for columns of specified data types to identify records that contain valid JSON data and parse the contents to create nested objects. This is a powerful way to document previously hidden data, improving documentation and governance.

Generating physical objects from logical models that have relationships with the "Containment" flag set

Relationships in logical models have properties to indicate whether the relationship is containing and its direction of containment. For platforms that support hierarchical structures there is a setting in the Generate Physical Model Wizard and in Compare/Merge that determines how entities and relationships are transformed into objects in the physical model. If the option "Create All As Tables Unless Contained" is selected in the Generate Database Wizard or "Consider all as Tables unless Contained" is selected in Compare/Merge Wizard, Data Architect will create containment relationships for every relationship in the logical marked as "Containing". Entities that are contained by another will become nested objects. Entities that are not contained by any other will become top level objects appropriate for that platform such as Tables or Collections.

Visualizing Hierarchical Structures

The hierarchical model containing Nested Objects linked via containment relationships can be visualized in two different ways:

Rolled Up

Here Nested Objects are rolled up into the top level object.

<<NADJA - INSERT "ROLLED UP VIEW.png"

Expanded

Here you can see the Nested Objects and Containment Relationships individually. This is useful to see which objects are shared across other tree branches.

<<NADJA - INSERT "EXPANDED VIEW.png"

Changing Between these Views

You can switch between these views using the ribbon bar feature Diagram and Display Options

<<NADJA - INSERT Diagram and Display Options for Rolled Up View.png>>

Changing Classes in Nested Object hierarchies

You may want to change the nature of how data is implemented in your physical models that support hybrid hierarchies. You may decide that what was an embedded or nested object should be promoted to being a top level object such as a Table or Collection. Likewise, you may wish to change the relationships between nested objects and top level objects from referencing to containment relationships and vice versa. We have provided tools to do this.

Users can easily change a relationship type for containment relationships. While viewing a containment relationship that you want to change the relationship type, click Switch Relationship Class in the ribbon bar. The following actions occur:

• If the line is referencing:

  1. ERDA removes the Foreign Key field/column in the child object/table.
  2. A dialog appears asking you to select the desired containing object, and offers Parent, Child, and Cancel options. Note that Child is the default.
  3. ERDA changes the relationship type to containing in the direction selected in the dialog.
  4. A new foreign key property is created in the Parent object.

• If the line is containing:

  1. ERDA removes the Foreign Key field/column in the parent object/table.
  2. A dialog appears asking you to select the desired referencing object, and offers Parent, Child, and Cancel options. Note that Parent is the default.
  3. ERDA changes the relationship type to Referencing in the direction selected in the dialog.
  4. A new foreign key property is created in the Child object, propagating the primary key of the parent.

The following image shows this feature using Google BigQuery:

See Also

• Databricks Support
• MongoDB Support
• JSON Support
• Collection Editor
• Creating and Editing Relationships