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.

Some Thoughts About INFRA Database Implementation

So with the logical database design done, my attention turns to implementation. I certainly intend for the database to be in a Relational Database Management System (RDBMS). Since I used Oracle’s SQL Developer Data Modeler (SDDM) to do the logical design, I used its ability to engineer to a relational design. At this point I don’t want to share my relational design, but you can see its outline in the logical design. It is a fairly straight implementation with the only major additions to what you saw in the logical design being surrogate primary keys for some tables, and intersection tables to implement many to many relationships. I also refined the attributes a bit, which became columns in the tables.

Physical Database

For the database implementation, I want to make sure that INFRA is database independent. I want it not to matter whether it is Oracle, SQL Server, PostgreSQL, DB2, MariaDB, or whatever. As long as it supports ANSI SQL, I’d like it to work with just a little configuration to have the application connect to the database. But it isn’t enough to support ANSI SQL, because some things I need are not part of the ANSI standard. For instance, Oracle, SQL Server and PostgreSQL all support Sequences to set the values of surrogate primary keys, but the code for getting the next value of a Sequence is non-standard and thus different. Look at the differences in the way my SERVICE table is defined for three different RDBMSs.

Oracle XE 11g

CREATE SEQUENCE service_seq;

CREATE TABLE service (
    service_id           NUMBER NOT NULL,
    service_name         VARCHAR2(127 CHAR),
    service_maker        VARCHAR2(127 CHAR),
    service_type_code    VARCHAR2(30 CHAR) NOT NULL,
    product_name         VARCHAR2(127 CHAR),
    product_version      VARCHAR2(127 CHAR),
    environment_code     VARCHAR2(30 CHAR) NOT NULL,
    database_type_code   VARCHAR2(30 CHAR),
    as_type_code         VARCHAR2(30 CHAR),
    server_id            NUMBER NOT NULL
);

ALTER TABLE service ADD CONSTRAINT service_pk PRIMARY KEY ( service_id );

CREATE OR REPLACE TRIGGER service_service_id_trg BEFORE
    INSERT ON service
    FOR EACH ROW
    WHEN ( new.service_id IS NULL )
BEGIN
    :new.service_id := service_seq.nextval;
END;
/

SQL Server 16

CREATE SEQUENCE service_seq AS INT START WITH 1 INCREMENT BY 1;

CREATE TABLE service (
    service_id           INT NOT NULL DEFAULT NEXT VALUE FOR service_seq,
    service_name         VARCHAR(127),
    service_maker        VARCHAR(127),
    service_type_code    VARCHAR(30) NOT NULL,
    product_name         VARCHAR(127),
    product_version      VARCHAR(127),
    environment_code     VARCHAR(30) NOT NULL,
    database_type_code   VARCHAR(30),
    as_type_code         VARCHAR(30),
    server_id            INT NOT NULL
);

ALTER TABLE service ADD CONSTRAINT service_pk PRIMARY KEY ( service_id );

PostgreSQL 9

CREATE SEQUENCE service_seq;

CREATE TABLE service (
    service_id           INT4 DEFAULT nextval('service_seq') NOT NULL,
    service_name         VARCHAR(127),
    service_maker        VARCHAR(127),
    service_type_code    VARCHAR(30) NOT NULL,
    product_name         VARCHAR(127),
    product_version      VARCHAR(127),
    environment_code     VARCHAR(30) NOT NULL,
    database_type_code   VARCHAR(30),
    as_type_code         VARCHAR(30),
    server_id            INT4 NOT NULL
);

ALTER TABLE service ADD CONSTRAINT service_pk PRIMARY KEY ( service_id );

About IDENTITY

Just a note about another way of generating values for surrogate primary keys. SQL Server has the concept of an IDENTITY constraint that generates these values and it is older than Sequences in SQL Server. I prefer not to use them, partly because it is more difficult to insert values with the primary key already set. This is why I have the Oracle trigger set to assign a value from the sequence only if the key is null in the INSERT command.  But I will admit that the fact that my career has been largely using Oracle may also be a factor.  The use of the DEFAULT clause in SQL Server and PostgreSQL does the same – the value is only assigned from the sequence if it is NOT in the INSERT. Oracle 12c and later has an IDENTITY type of column that creates a sequence and assigns values from it behind the scenes. It also has enhanced the DEFAULT clause so you no longer need a trigger if you want an explicit sequence used instead of an implicit one. PostgreSQL does a similar thing by creating a hidden sequence for SERIAL columns.

Benefits of SDDM for Physical Database

The nice thing about using SDDM for generating your DDL for implementing your database is that it understands the differences between a number of RDBMSs and can generate SQL DDL for them from the same relational design. For generated keys, it gives you several options for how to generate – whether triggers, or default values, or Identity columns. And if it doesn’t do what you need to do out of the box, you can write your own generation code. See my post on scripting SDDM and a later example for more information.
Next time, I’ll talk about some application implementation considerations and options.