Design schemas

Procedure

1. Log on to the DMS console V5.0.
   Note To switch to the previous version of the DMS console, click the icon in the lower-right corner of the page. For more information, see Switch to the previous version of the DMS console.
2. In the top navigation bar, click Database Development. In the left-side navigation pane, choose Schema Change > Schema Design.
   Note If you are using the previous version of the DMS console, move the pointer over the More icon in the top navigation bar and choose Schemas > Schema Design.
3. On the Schema DesignTickets page, click Schema Design in the upper-right corner.
   Note On the Schema DesignTickets page, you can view detailed information about historical tickets for schema design.

   
4. Set the parameters that are described in the following table and click Submit.

   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 after you specify a database.
   Change Stakeholder	The stakeholders of the schema design. All the 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 tabs 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. On the Create Physical Table tab of the data_modify table, confirm the schema information.
      • Basic Info
      • Column Info
      • Index Info
   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 development standards that are configured in the default security rules. In this example, DMS reports two errors and one warning. DMS supports nearly 40 development standards to help you create tables and modify columns and indexes. You can configure the development standards in security rules as required.
   5. Change the schema of the data_modify table as prompted and click Save.
      • On the Basic Info tab, set the Remarks parameter to Test and the Character set parameter to utf8.
      • On the Column Info tab, rename the desc column user_desc.
   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 modify, preview, or delete the schema that you created.

   7. Click the Create Physical Table tab of the big_table 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 stakeholders:
   You can add other developers as stakeholders to review the new schemas 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 schemas are reviewed, publish them to the development database:
   1. On the Project Homepage tab, click Perform Changes to Base Database.
   2. In the Perform Changes to Base Database panel, set the Execution Strategy parameter to Execute Now. After you confirm that the SQL statements are valid, click OK.
      Note Valid values of the Execution Strategy parameter:

      • Execute Now: DMS immediately changes the schemas after the ticket is approved.
      • Schedule: After the ticket is approved, DMS changes the schemas at the specified point in time.
   3. After a DMS administrator approves the ticket, DMS publishes the new schemas 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 schemas.
      Note After the schemas are verified, you can publish the schemas 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 schemas to a production database:
   1. On the Project Homepage tab, click Perform Changes to Target Database.
   2. In the Perform Changes to Target Database panel, 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 schemas 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.