CODEWITHSUNDEEP
sqlstandardsansi-sql
psycoperl
psycoperl

Reputation: 150

How to ensure SQL database design will be cross platform

I am working on a project where my current development environment is based on MySQL, while the production environment may be either MySQL or Microsoft SQL Server. I am trying to make sure that the SQL I am writing to build my database tables will be cross platform.

I have already found that the ZEROFILL attribute is non-standard, so I am assuming that it would not be available outside of MySQL. 

CREATE TABLE
 `scfe`.`tbl_UserRoles` ( 
 `UserRoleRecID` INT(7) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'User Role Rec ID' ,
 `UserADID` CHAR(50) NOT NULL COMMENT 'User ADID FK - tbl_Users.UserADID' ,
 `UserRole` CHAR(150) NOT NULL COMMENT 'User Role FK - systbl_SYS_Roles.UserRoleID' ,
 `Status` ENUM('A','I') NOT NULL DEFAULT 'I' COMMENT 'User Role Status - A = Active, I = Inactive // Default = I' ,
`RoleDateAdded` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP , 
`RoleDateModified` DATETIME on update CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , 
PRIMARY KEY (`UserRoleRecID`), 
INDEX `Index_UserADID` (`UserADID`), 
INDEX `Index_UserRole` (`UserRole`), 
INDEX `Index_RoleStatus` (`Status`))
 ENGINE = MyISAM;

As I want to make sure that the code will easily transition between systems, are there other features/options that would not be cross-platform (for example, based on the code above is there a default CURRENT_TIMESTAMP? the on update Are there alternatives that should be used?

I know that the engine line would not need to be used outside of MySQL.

Upvotes: 0

Views: 257

Answers (1)

user330315
user330315

Reputation:

There is no way you can write a single CREATE TABLE statement that works the same with MySQL and SQL Server and results in what you have now, e.g. generating an ID would require an identity(...) option in SQL Server where you use auto_increment in MySQL. Neither of those options are standard SQL (that would be generated always as identity).

Data types are the next problem. Especially datetime/timestamp types are sufficiently different that you won't have much luck there. Btw: stop using char() - that type has only disadvantages and absolutely no advantages whatsoever.

There is no way you can use something like an ENUM in a cross-DBMS design. Use a lookup table instead.

Constraints and indexes are the next problem. Column and table comments as well (SQL Server only "supports" them by calling a stored procedure that stores "extended attributes).

I would recommend to use e.g. Liquibase with the XML format which can easily be used against a variety of database products. e.g. Liquibase knows how to create a "generated ID" column if you use autoIncrement="true" - it will use auto_increment for MySQL, identity() for SQL Server and serial for Postgres.

If you use "standard JDBC" data types, Liquibase also know how to translate them. e.g. timestamp will become datetime in SQL Server.

Your statement would result in something along the lines of:

<!-- lookup table for status -->
<createTable tableName="role_status"  schemaName="scfe" remarks="User Role Status - A = Active, I = Inactive // Default = I">
  <column name="status_id" type="integer">
    <constraints nullable="false" primaryKey="true" primaryKeyName="pk_role_status" />
  </column>
  <column name="status_code" type="varchar(1)" />
</createTable>

<createTable tableName="user_roles" schemaName="scfe">
  <column name="userrolerecid" type="integer" autoIncrement="true" remarks="User Role Rec ID">
    <constraints nullable="false" primaryKey="true" primaryKeyName="pk_user_roles" />
  </column>
  <column name="useradid" type="varchar(50)" remarks="User ADID FK - tbl_Users.UserADID">
    <constraints nullable="false"/>
  </column>
  <column name="user_role" type="varchar(150)" remarks="User Role FK - systbl_SYS_Roles.UserRoleID'">
    <constraints nullable="false"/>
  </column>
  <column name="status_id" type="varchar(1)">
    <constraints nullable="false"/>
  </column>
  <column name="role_date_added" type="timestamp">
    <constraints nullable="false"/>
  </column>
  <column name="role_date_modified" type="timestamp"/>
</createTable>

<addForeignKeyConstraint constraintName="fk_user_role2status" 
                         baseTableName="user_roles" 
                         baseColumnNames="status_id" 
                         referencedTableName="role_status" 
                         referencedColumnNames="status_id" />

<addForeignKeyConstraint constraintName="fk_user_role2role" 
                         baseTableName="user_roles" 
                         baseColumnNames="user_role" 
                         referencedTableName="roles" 
                         referencedColumnNames="user_role_id" />

<addForeignKeyConstraint constraintName="fk_user_role2adid" 
                         baseTableName="user_roles" 
                         baseColumnNames="useradid" 
                         referencedTableName="users" 
                         referencedColumnNames="useradid" />

<createIndex indexName="index_useradid" tableName="user_roles">
  <column name="useradid"/>
</createIndex>

<createIndex indexName="index_userrole" tableName="user_roles">
  <column name="user_role"/>
</createIndex>

<createIndex indexName="index_rolestatus" tableName="user_roles">
  <column name="status_id"/>
</createIndex>

This can be extended to cope with things that need to be DBMS specific (e.g. a data type for UUID) either by using parameters or by only running certain things on against specific DBMS products (e.g. to create check constraints or special index types)

Upvotes: 2

Related Questions