Associate Similar Objects Using User-Defined Mappings
Go Up to Developing the Logical Model
When consolidating various data assets across the enterprise, quite often the logical and physical model are completely unrelated. When new physical models are brought in via the Add New Physical Model wizard or Compare and Merge wizard, it is difficult to "map" existing logical entities to the physical tables. The physical objects may already be denormalized and complex (one-to-many, many-to-one) relationships may exist between the logical and physical objects. In this case, there is a gap between what is logically modeled and what is physically implemented. ER/Studio helps modelers map completely unrelated objects across models within a dm1 file using User-Defined Mappings.
Create user-defined mappings to relate logical entities to physical tables. The UDM can relate any objects of the same type. Once the UDM is created, you can see a graphical tree/node representation of the mapping on the Where Used tab in the Entity Editor or Table Editor. In the screenshot below, the entities mapped to Customer are marked with a little pushpin icon . On the right of the screen are icons that let you edit the mapping or edit the user-defined mappings , and switch the entity editor to view or edit the details of the object selected. For information purposes you also map a Table to an Entity and edit that mapping from the Where Used tab.
One use of the UDM is to back-construct the relationships shown in the Where Used tab.
For example, frequently a legacy physical model must be imported and added to a mature logical model (Using the Add New Physical model wizard: from the Main menu, Model > Add New Physical Model). You must relate the two models and can use the user-defined mapping to create and document the relationship.
Denormalization information for the imported physical model likely is not available. Therefore, ER/Studio Data Architect has no denormalization mapping information to display on the Where Used tab. The UDM system allows you to build the mappings with no restrictions. You can map attributes to columns and entities to tables by right-clicking any of the models listed and selecting the related objects.
Create User-Defined Mappings
- In the Data Model Explorer or Data Model Window, right-click the entity or table that you want to associate with other entities or tables.
- Click the Where Used icon
- To associate the object with any entity/table in the model, in the Where Used dialog, double-click the Where Used entry (the one with the pencil icon next to it) or right-click the pencil icon and select Edit Mappings. Click the target entity/table and the Definition and Attachment Bindings tabs appear.
The following helps you complete the options of the Edit Mappings dialog:
Enter or edit a definition for the user-defined mapping. If the target database supports it, ER/Studio Data Architect adds this definition as a table comment when generating SQL code.
Attachment Bindings tab
Bind an external piece of information, or attachment to the user-defined mapping. You can also remove an attachment from an object, override an attachment binding's default value, or change the position of a bound attachment. To override the value of the attachment you have moved to the Selected Attachments grid, double-click the Value field of the target attachment. ER/Studio Data Architect opens the Value Override Editor or a list depending on the attachment datatype. Attachments are created in the Attachments folder of the Data Dictionary. For more information, see Attaching External Documents to the Data Model.
- You can map an entity to tables in disparate physical models.
- Once you have created a user-defined mapping, to view or edit the mapping you can open a Table Editor or Entity Editor and then select the Where Used tab. You can also right-click a table in the diagram and select Edit User-Defined Mappings.
- You can also edit the mappings from the Where Used tab in the Table Editor or Entity Editor by right-clicking the model you want to editor and clicking the Edit option. When editing the user-defined mappings, you can also edit the definition and the attachments bound to the user-defined mapping.