Design schemas

Example

1. Log on to the DMS console as a regular user.
2. In the top navigation bar, choose Schemas > Schema Design.
   
3. On the Schema Design tab, click Schema Design in the upper-right corner.
   Note On the Schema Design tab, you can view detailed information about historical tickets for schema design.

   
4. In the dialog box that appears, set the parameters as required and click Submit. The following table describes the parameters.

   Parameter	Description
   Project Name	The name of the project, which helps identify the project. In this example, the name is POC Demonstration.
   Project description	The purpose or objective of the project, which accelerates the approval process.
   Change Base Database	The database on which you want to perform schema design operations. In this example, select the poc_dev database. This database adopts the default security rules. When you create a schema design ticket, DMS creates a schema design process based on the R&D process of the default security rules. The schema design process includes the design and publishing of a schema.
   Security Rules	The security rules that you want to apply. DMS automatically selects security rules based on the selected database.
   Change Stakeholder	The stakeholders of the schema design. All specified stakeholders can view the ticket details and assist developers in accelerating the approval process. Irrelevant users other than DMS administrators and database administrators (DBAs) are not allowed to view the ticket details.

5. Create physical tables:
   1. Click Import Table Creation Statements.
   2. In the Import Table Creation Statements dialog box, enter the following SQL statements and click OK.

      These SQL statements are used to create the big_table and data_modify tables. After you click OK, two Create Physical Table dialog boxes appear.

      CREATE TABLE `big_table` (
        `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
        `name` varchar(64) NOT NULL COMMENT 'Name',
        `long_text_a` varchar(1024) DEFAULT NULL COMMENT 'Text A',
        `long_text_b` varchar(1024) DEFAULT NULL COMMENT 'Text B',
        PRIMARY KEY (`id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Schema change of a large table';
      
      CREATE TABLE `data_modify` (
        `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
        `name` varchar(256) NOT NULL COMMENT 'Name',
        `phone` varchar(32) DEFAULT NULL COMMENT 'Phone number',
        `sex` varchar(32) DEFAULT NULL COMMENT 'Gender',
        `email` varchar(256) DEFAULT NULL COMMENT 'Email address',
        `desc` varchar(1024) DEFAULT NULL COMMENT 'Remarks',
        PRIMARY KEY (`id`)
      ) ENGINE=InnoDB DEFAULT ;

   3. In the Create Physical Table dialog box of the data_modify table, confirm the schema information.
   4. Click Save. The Precheck Result message appears.

      In the data_modify table, the Remarks and Character set parameters are left empty, and the database keyword desc is used as the column name.

      Note DMS verifies the preceding SQL statements for table creation based on the R&D specifications that are configured in the default security rules. In this example, DMS reports two errors and one warning. DMS supports nearly 40 R&D specifications to create tables and modify columns and indexes. You can configure the R&D specifications in security rules as required.
   5. Change the schema of the data_modify table as prompted and click Save.
   6. In the Precheck Result message, click Confirm Changes and Submit to Save.

      Then, you are navigated to the Project Homepage tab. On this tab, you can view that the data_modify table is created but not published to a development database. On the Project Homepage tab, you can edit, preview, or delete the schema that you created.

   7. Click Create Physical Table and confirm the schema of the big_table table.
   8. After you confirm the schema, click Save.
   9. In the Precheck Result message, click Confirm Changes and Submit to Save.

      The big_table and data_modify tables are created.

6. Add new stakeholders:
   You can add other developers as stakeholders to review the new schema and accelerate the ticket approval process. This facilitates collaborative development.

   1. On the Project Homepage tab, click the icon.
   2. In the Modify Change-related Personnel dialog box, click Add and select one or more stakeholders.
   3. Click Update Stakeholder.
7. After the schema is reviewed, publish the schema to the development database:
   1. On the Project Homepage tab, click Perform Changes to Base Database.
   2. In the Perform Changes to Base Database dialog box, set the Execution Strategy parameter to Execute Now. After you confirm that the SQL statements are valid, click OK.
      Note Valid values of Execution Strategy:

      • Execute Now: DMS immediately changes the schema after the ticket is approved.
      • Schedule: After the ticket is approved, DMS changes the schema at the specified point in time.
   3. After a DMS administrator approves the ticket, DMS publishes the new schema to the development database.
      Note

      • Based on the default security rules, you must submit tickets for schema changes. You can also use custom security rules. For example, you can specify that no approval is required for schema changes in development databases. You can also specify the users who approve schema change tickets in production databases. For more information about how to approve tickets, see Approve a schema design ticket.
      • In the Approval step, you can click View Approval Details to view the approval progress.
   4. View the table details of the poc_dev database on the SQLConsole tab and verify the schema.
      Note After the schema is verified, you can publish the schema to a production database.
   5. On the Project Homepage tab, click Go to Next Node.
   6. In the Go to Next Node message, click Go to Next Node.
8. In the Release the schema step, perform the following operations to apply the new schema to a production database:
   1. On the Project Homepage tab, click Perform Changes to Target Database.
   2. In the Perform Changes to Target Database dialog box, select the poc_prod database, set the Execution Strategy parameter to Execute Now, and then click OK.
      Note You can specify more than one production database.
   3. After a DMS administrator approves the ticket, DMS publishes the new schema to the poc_prod database.
      Note For more information about how to approve tickets, see Approve a schema design ticket.
   4. On the Project Homepage tab, click Go to Next Node.
   5. In the Go to Next Node message, click Go to Next Node.

      The ticket is closed. You can click each step to view the change and publishing records.