Data Management (DMS) provides the schema design feature. This topic describes this feature and shows you how to design schemas.

Prerequisites

  • A MySQL, Distributed Relational Database Service (DRDS), OceanBase, or PolarDB for MySQL database is added to DMS.
    Note Other database types will be supported in the future.
  • The control mode of the instance is Security Collaboration.

Background information

When you develop projects, process new business requirements, or optimize business operations, tables may be created or modified for schema design. For example, you may need to add or delete fields, add or delete indexes, modify field attributes, or modify index composition. In such cases, you can use the schema design feature of DMS.

  • This feature allows multiple users to collaboratively design schemas on web pages.
  • This feature allows you to send verified scripts to other environments with ease. This ensures consistency between schemas in different environments.

Usage notes

  • You cannot submit a ticket to delete tables that already exist before you create the ticket. You can delete tables that are created after you create the ticket.
  • After changes are made to a database, the metadata is also synchronized. You can execute the show create table table_name or desc table_name statement on the SQLConsole tab to check whether the changes take effect.

Procedure

  1. Log on to the DMS console.
  2. In the top navigation bar, move the pointer over the More icon and choose Schemas > Schema Design.
  3. Click Schema Design in the upper-right corner.
  4. Set the following parameters:
    • Project Name: the name of the project or requirement.
    • Project description: the business background of the project, such as the purposes and objectives of the project.
    • Change Base Database: the database to which changes are made. Make sure that you have one or more of the query, export, and change permissions on the database. Enter the prefix of a database name to search for databases. Only databases in the test and development environments on which you have permissions are displayed.
    • Change Stakeholder: the stakeholders of the changes. The specified stakeholders can view the ticket details and assist in the approval process. Irrelevant users other than DMS administrators and database administrators (DBAs) are not allowed to view the ticket details.
  5. Click Create Ticket.
  6. Perform data definition language (DDL) operations as needed. Examples:
    • To create a table, click Create Physical Table.
    • To modify a table, find the table in the left-side table list. Click the table name and select Design Table.
      Note When you save the changes, DMS checks the changes based on the configured design specifications. If the changes do not comply with the specifications, a notification message appears. For more information, see Schema design specifications: Guarantee the existence of fields.
  7. Click Perform Changes to Base Database.
  8. Set the Execution Strategy parameter and click Submit for Execution.
  9. Wait for approval.
    Note You can view the approvers of each approval node. The ticket is submitted to the next approval node when one of the approvers of the current node gives approval.
  10. Click Go to Next Node.
    • After the ticket is approved, DMS performs changes at the scheduled time. If you do not specify the execution time, the changes are automatically performed after the ticket passes the last approval node. You can view the execution status and operations logs. After all changes are performed, you can repeat the preceding procedure to modify the design. If you confirm that the design is completed, click Go to Next Node.
    • The configured design specifications determine whether you can go back to the previous node and modify the design after the ticket is submitted to the next node.
  11. Click Perform Changes to Target Database.
  12. Set the Target Database and Execution Strategy parameters and click Submit for Execution.
    Note In this example, the database to which changes are made must be in the production environment.
  13. Wait for the ticket to be approved and the changes to be performed.
  14. Click Go to Next Node.
    Then, the procedure ends and the ticket is closed.

References

Schema Design
Note Only DMS administrators or DBAs are allowed to configure security rules for schema design.