SDDM Physical Model for MySQL

As an example, I’ve imported the Relational Design for the HR schema into SDDM from my Oracle 11g XE Database. When you import from a data dictionary, SDDM creates a Relational Model, but it also creates a Physical Model for the database from which you imported. If you import from an unsupported database like MySQL, it will default to Oracle, but you can set the target to one of the other supported databases if you like. But I recommend that you choose from the Tools menu to run the RDBMS Site Editor, and create a new site for your MySQL database. By creating a new Site, you can distinguish your physical models that have been customized for your non-standard database.
This will require you to choose a supported database as the database type. So we have to choose the one that is as close as we can get. I did a lot of research and while there is no perfect solution, as far as I can determine, SQL Server was the best choice. There are some major differences that we will need to deal with, but at least the datatypes are fairly close, because they support the ANSI SQL standard a little better than Oracle. For instance, a text field is VARCHAR, not VARCHAR2. So here is my new RDBMS Site as shown in the Site Editor:

So, now I can create a new Physical Model for my MySQL database. The browser shows my Relational Model, the original Physical Model for my Oracle 11g XE database, and the new Physical Model.

DDL Differences for MySQL

What other adaptations do we need? For my purposes, the main change that I need is to change the DDL for generating surrogate primary keys from a non-repeating sequence of numbers. MySQL does this with the AUTO_INCREMENT attribute of the primary key column. So whenever I mark a column in the Relational Design as an auto-increment column, I want the CREATE TABLE command that is generated to have AUTO_INCREMENT, not the IDENTITY attribute that is typical of SQL Server and Oracle 12 and later, or the generated SEQUENCE and trigger that is typical of Oracle versions before 12c. And I want to use the “Start with” and “Increment” values that I put into the Relational Design, in case I don’t want to use the defaults – 1. Here’s EMPLOYEES.EMPLOYEE_ID marked as Auto Increment:



Table and column comment syntax is different too. In SQL Server, you have to call a built-in procedure to add the comment to the table or column. In Oracle, you use the “COMMENT ON” syntax. But in MySQL, there is a COMMENT attribute of the column or of the table. So I want “Comments in RDBMS” generated with the proper syntax.

Syntax for check constraint is pretty similar to syntax in Oracle or SQL Server, but they aren’t enforced in MySQL before version 8, so I want a SQL comment to warn the developer. There are a few CREATE TABLE clauses that are unique to MySQL, like ENGINE, but the defaults are reasonable, so I leave them out. If you need them, you may have to add some user-defined properties for them to SDDM and then the transformation script needs to use those properties in DDL generation – I may do that in a future version, but right now my script doesn’t have them.
In my next post, I’ll share my SDDM Transformation script, and explain how it works.