Tutorials: Working with Hierarchical Models

We model information in ER/Studio using Entity Relation Diagrams. These can be converted directly to relational models that represent relational databases. Data assets such as MongoDB, JSON and Google BigQuery have hierarchical structures. These hierarchical structures also employ denormalization to improve performance with repeated substructures. Data Architect converts relational logical models to denormalized hierarchical structures and vice versa using the Generate Database, Reverse Engineer, Import from File and Compare/merge wizards. To make this process repeatable, logical data models are enhanced to include additional information to help with this process.

Specifying Containment in the Logical Model

In the following logical data model diagram, notice that some lines show the text "Parent contains Child" or "Child contains Parent". This allows you to specify how objects will be embedded in any hierarchical physical models you generate from it. This will drive both the Generate Physical Model and Compare/Merge wizards.

You can set two properties of the relationship to control this.

• Containing - one object will be contained in the other as a Nested Object in the physical
• Parent is Container - the direction of containment

Generating the Physical Model (MongoDB)

The Generate Database wizard differs for each target platform but for MongoDB models there will be the following options: For MongoDB on Page 2 of the wizard

Propagated Foreign Key properties can be removed from generated objects or made as native fields which may be useful later to connect objects together elsewhere via code.

There is an option to select how Collections will be created from entities:

• Convert All Entities - all logical entities become Collections
• Convert Entities with PKs - all logical entities with primary keys become Collections
• Leave All as Nested Objects - all logical entities become Nested Objects
• Convert if Not Contained - takes a rules-based approach as detailed below using the containment properties set in the logical

Convert if Not Contained

The generator wizard will follow the following steps:

1. For each entity in the logical model create an object. Primary key attributes will become standard fields.
2. Logical relationship lines will generate either a referencing relationship or a containment relationship.
3. For each generated object the wizard will review whether the object has a containing relationship from any other object. If it does it will become a Nested Object. If not it will become a top level Collection.
4. All Collections are given a surrogate primary key "_id"
5. For referencing relationships primary keys are propagated

For the above logical model we would generate the following model using this logic

Notice the following:

• The borders of the objects. Collections have square corners and Nested Objects rounded corners.
• We have created four Collections: book, patron, library, and publisher. The other entities have generated Nested Objects.
• The entities section and chapter are nested inside the book.
• address has been fully denormalized as nested objects inside publisher, library, and book.
• checkout has only been nested inside book but retains references to the Collections library and patron.

I didn't get what I wanted

Once you have generated your physical models you will see a structure of objects with Collections and Nested Objects based on the rules used by the wizard. If you don't get what you expect you have the following options:

1. Modify the Containment properties in the logical model and use Compare/Merge between the logical and physical to propagate the changes
2. Modify the Physical model directly. The ribbon bar has a tool 'Switch Object Class', on the Insert tab, to convert a Collection to a Nested Object and vice versa. You can then propagate changes back to the logical

The Rolled Up View

The object view is useful to understand the reuse of objects and edit the cardinality to Nested Objects. You can also select a rendering of the model that is easier to visualize the hierarchical nature of the model.

1. On the Diagram tab of the ribbon bar select Diagram/Display Options
2. On the Object tab select the checkbox 'Roll Up Contained Objects'

The MongoDB model we generated above will now look like this

Notice that the Nested Objects are rolled up into the parents.

Rapidly Creating a Hierarchical Physical Model

You may want to bypass the process of creating logical and jump straight to creating a physical model. You may want to use Compare/merge to later create your logical model.

1. Create a new model and add an entity into the logical model
2. Generate a Physical model for the MongoDB platform
3. You can insert objects onto the physical model directly using the Insert tab on the ribbon bar.
4. You can insert fields directly into the objects just using the keyboard. Hold the shift key and right-click inside the object. It will open a text box.
5. Enter the name of the field
6. Hit the enter key. A dialog appears allowing you to select the data type of the field. Select string and hit enter. The field is created.
7. A new text box appears. Give it a name and hit enter again.
8. In the datatype selection box, select Object and select the array checkbox. Hit Enter. A dialog will appear allowing you to create a new reusable Nested Object. Hit Yes
9. In the next new field specify a name for the new field but before you hit Enter press the Tab key. This will indent the field making a field of the object you just created. Now hit enter and specify the data type of the field.

As you build up your model you can copy fields across the model using the Move tool to move fields or with the Control key to copy fields