ER/Studio Data Architect is a flexible modeling platform that can usually be easily adapted to work with new platform constructs, such as new data types added by a DBMS vendor. All the data types and mappings in ER/Studio are implemented through file-driven data types and mappings, which you can easily modify and extend.
To allow for easy customization, I make copies of the shipped datatypes and mappings in my own work folders. That allows me to modify them at low risk since I can easily overlay them with original copies if the result is not what I expect.
Once I have created the work folders, I can set the paths to use in the Data Architect application by selecting the menu and options for Tools-> Options-> Directories.
Here are the defaults after initial installation on my computer:
Simply create a folder to hold your datatype mappings, copy the datatype mapping files into it, then update the path in your options. You will need to do so in order to modify them, since you will not be able to change those in the Program Data folder.
In this example, I am going to add a new JSON datatype so that I can use it in both logical models and in MySQL. The way to do this is through the datatype mapping editor.
I want to make this datatype available for all MySQL 5.x versions, so I first modify the MySQL mappings by clicking the “New Custom Datatype” button and creating the new datatype called JSON:
New datatypes to be used need to be added using one of the system templates so that it is available for all models and platforms.
I then add a new physical mapping to that same template (or a custom mapping template if desired) to indicate what the mapped physical datatype will be:
The last step is to update my model to use the new mappings. When I save this mapping, I also get an information message reminding me of that:
Therefore, I simply go into the model options dialog and click the “Update Datatype Mapping” button at the top of the dialog:
Now, I can proceed with my modeling, using the new datatype as needed. NOTE: When generating a physical model from a logical model, the wizard also allows you to specify a specific data mapping template.
Here is a very simple table that was created with my new datatype:
By opening the DDL tab in the editor, you can also see that the generated DDL syntax is correct:
This discussion used MySQL as an example, but custom datatype mapping can be applied to any other platforms supported by ER/Studio as well. You can also use these techniques (and others) to customize ER/Studio and use platforms that are not named as well. If you wish to see a more detailed example, here is a previous post discussing Redshift.
Understanding these capabilities will provide you with huge productivity benefits and the ability to adapt ER/Studio Data Architect to conquer your modeling challenges.
