All Products
Search
Document Center

Data Management:Design schemas

Last Updated:Apr 11, 2024

Data Management (DMS) provides the schema design feature. This feature allows you to design the schemas of tables in a database based on specific R&D standards. You can customize R&D processes for different lines of business based on your business requirements to ensure the consistency of schemas among multiple environments, such as the development environment, test environment, and production environment. This topic describes how to use the schema design feature. In the following example, a physical database is used.

Prerequisites

  • The database is of one of the following types:

    • MySQL: ApsaraDB RDS for MySQL, PolarDB for MySQL, ApsaraDB MyBase for MySQL, PolarDB-X, and MySQL databases from other sources

    • OceanBase

    • Oracle

    Note

    This feature supports both physical databases and logical databases.

  • The database instance to which the database belongs is managed in Secure Collaboration mode. For more information, see View the control mode of an instance.

  • The environment type of the database for which you want to design schemas meets the environment requirement specified in the security rules for schema change. For more information, see Change the environment type of an instance.

Procedure

The following operations describe how to create a table by submitting a schema design ticket, and synchronize the table schema to the poc_dev destination development database and the poc_prod destination production database. This topic provides references for you to quickly learn how to use the schema design feature of DMS. For more information, see Design schemas.

Step 1: Create a schema design ticket

  1. Log on to the DMS console V5.0.
  2. In the top navigation bar, choose Database Development > Schema Change > Schema Design.

    Note

    If you use the DMS console in simple mode, move the pointer over the 2023-01-28_15-57-17.png icon in the upper-left corner of the console and choose All Features > Database Development > Schema Change > Schema Design.

  3. On the Schema DesignTickets page, click Schema Design in the upper-right corner.

  4. Set the parameters for creating a schema design ticket and click Submit. The following table describes the parameters.

    Parameter

    Required

    Description

    Project Name

    Yes

    The name of the project. Enter a name that can help you identify the project.

    Project description

    Yes

    The business background of the project, such as the purpose or objective of the project. This reduces unnecessary communication.

    Change Base Database

    Yes

    The database on which you want to perform schema design operations. In this example, a database in Security Collaboration mode is selected. The type of the environment in which the database resides must conform to the R&D standards that correspond to the security rules of the database. For more information about the R&D process, see Schema design. In this example, the poc_dev development database is used as the change base database.

    Security Rules

    No

    The security rules that you want to apply. DMS automatically selects security rules after you specify a database.

    Associated Iterations

    No

    The iteration to be associated with the project. To select an iteration, click Select/Change Iteration. In the Iterations dialog box, find the iteration and click Associate in the Actions column.

    Change Stakeholder

    Yes

    The stakeholders of the schema design. All the specified stakeholders can view the ticket details and assist developers in the development and approval processes.

    Note

    Irrelevant users other than DMS administrators and database administrators (DBAs) are not allowed to view the ticket details.

Step 2: Create a physical table

  1. Create a physical table

    In addition to the following methods, you can create a physical table on the SQLConsole tab. For more information, see the "Create a table" section in Manage the Wide Column model in the Tablestore console.

    • Use the GUI

      1. Click Create Physical Table.

      2. Enter the basic information about the table and modify the fields and indexes of the table.

      3. Click Save.

    • Execute SQL statements

      1. Click Import SQL Statements.

      2. In the dialog box that appears, enter the CREATE TABLE or ALTER TABLE statement as required and click OK.

        For example, you can execute the following statement to create a table named data_modify:

        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',
          `remarks` varchar(1024) DEFAULT NULL COMMENT 'Remarks',
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Personal information';
      3. On the Create Physical Table: data_modify tab, confirm the table information and schema and click Save.

  2. In the Precheck Result message, view the precheck result.

    • If the precheck is passed, click Confirm Changes and Submit to Save.

    • If only a warning is displayed, click Ignore the warning and continue to save.. Alternatively, click Disable, modify the table schema based on the R&D standards, and then click Save.

    • If an error is reported, check the cause of the error, click Disable, modify the table schema based on the R&D standards, and then click Save.

    Note
    • DMS verifies the preceding SQL statements for table creation or modification based on the R&D standards that are configured in the default security rules, and then reports errors and warnings, if any.

    • DMS provides nearly 40 R&D standards to help you create tables and modify fields and indexes. You can configure the R&D standards in security rules as required.

    After the precheck is passed, you are navigated to the Project Homepage tab. On the Tables Changed in Project tab, you can view the table that you modified or created and modify or delete other tables as required.

Step 3: Apply the schema changes to the destination development database

  1. Click Perform Changes to Base Database.

  2. In the Perform Changes to Base Database panel, view the base database, execution strategy, and schema change script, and click Submit.

After the schema change operation is approved, the system automatically applies the schema changes to the base database.

Note
  • You can check whether the schema changes are performed as expected on the SQLConsole tab of the base database.

  • 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 can approve schema change tickets in production databases.

Step 4: Apply the schema changes to the destination production database

  1. Click Perform Changes to Target Database.

  2. In the Perform Changes to Target Database panel, select the destination database, enter script in the Script to Execute field, and then click Submit.

    Note

    You can also click Add Multiple Databases to execute the script in multiple production databases.

    After the schema change operation is approved, the system automatically applies the schema changes to the production database. You can check whether the schema changes meet your expectations on the SQLConsole tab of the production database.

  3. Click Go to Next Node. In the message that appears, click Go to Next Node.

    After the ticket is closed, you can click each step to view the change and publishing records.