Containment Relationships
Go Up to Creating and Editing Relationships
Overview
Use containment relationships in physical models to support hierarchies such as JSON, MongoDB, Google BigQuery, Databricks, and 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 are 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
- On the Data Model Explorer, click the Data Model tab.
- Choose Insert > Relationships > Containment Relationship. This step changes the cursor icon and allows you to select the objects in the main window used to create the relationship.
- Click the object containing the other object.
- Click the object contained within the previously-clicked object.
Double clicking on the relationship line opens the Containment Relationship Editor where you can edit settings.
The 1 indicates a single nested object while * 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, and Databricks, Data Architect creates nested objects linked via containment relationships.
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. This feature is available only for the following platforms that support embedded JSON:
- Azure SQL DB
- Azure Synapse Analytics
- MS SQL Server 2019, 2022
- Oracle 19c, Oracle 23ai
- PostgreSQL
- Snowflake
Generating physical objects from logical models that have relationships with the "Containment" flag set
Relationships in logical models include properties that indicate whether the relationship is containing and the 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 if Consider all as Tables unless Contained is selected in the Compare/Merge wizard, ERDA creates containment relationships for every relationship in the logical marked as "Containing". Entities that are contained by another become nested objects and entities that are not contained by any other 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 either rolled up or expanded views.
You can switch between these views using the Diagram and Display Options in the ribbon bar 
.
In the Rolled Up view, Nested Objects are rolled up into the top-level object.
In the Expanded view, you can view Nested Objects and Containment Relationships individually. This view is useful to see which objects are shared across other tree branches.
Changing Classes in Nested Object hierarchies
ER/Studio Data Architect allows you 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.
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:
- ERDA removes the Foreign Key field/column in the child object/table.
- A dialog appears asking you to select the desired containing object, and offers Parent, Child, and Cancel options. Note that Child is the default.
- ERDA changes the relationship type to containing in the direction selected in the dialog.
- A new foreign key property is created in the Parent object.
- If the line is containing:
- ERDA removes the Foreign Key field/column in the parent object/table.
- A dialog appears asking you to select the desired referencing object, and offers Parent, Child, and Cancel options. Note that Parent is the default.
- ERDA changes the relationship type to Referencing in the direction selected in the dialog.
- 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:
