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.