Design schemas - Data Management - Alibaba Cloud Documentation Center

Table schemas change as you add fields, drop indexes, or restructure columns during development. Schema design in Data Management Service (DMS) validates these changes against your organization's R&D standards -- for example, preventing deletion of fields used in production. You can customize R&D processes for different lines of business. After validation, promote verified schemas from development through testing to production in a few clicks. Multiple users can collaborate on the same schema change, and cross-environment publishing keeps schemas consistent across environments.

This topic uses a physical database as an example. For logical tables, see Schema design for logical tables.

Before you begin

Make sure you have:

A database of one of the following types:
- MySQL: ApsaraDB RDS for MySQL, PolarDB for MySQL, ApsaraDB MyBase for MySQL, PolarDB-X, or a MySQL database from another source
- OceanBase
- Oracle
A database instance managed in Secure Collaboration mode. For details, see View the control mode of an instance
A database whose environment type meets the requirements in the security rules for schema change. For details, see Change the environment type of an instance

Note

Schema design supports both physical databases and logical databases.

Usage notes

You cannot submit a schema design ticket to delete existing tables. You can only delete tables that were created after the ticket was opened.
After changes are applied to a database, the metadata is also synchronized.

Note

Run show create table table_name or desc table_name on the SQLConsole tab to verify that the changes took effect.

Create a schema design ticket

Log on to the DMS console V5.0.
In the top navigation bar, choose Database Development > Schema Change > Schema Design.
Note
If you use the DMS console in simple mode, hover over the icon in the upper-left corner and choose All functions > Database Development > Schema Change > Schema Design.
On the Schema Design tab, click Schema Design in the upper-right corner.

Configure the following parameters and click Submit.

Note

Only DMS administrators, database administrators (DBAs), and designated stakeholders can view ticket details. Other users cannot access them.

Parameter	Required	Description
Change Base Database	Yes	The database where you perform schema design operations. Select a database in Secure Collaboration mode. The environment type must conform to the R&D standards configured in the security rules. For details, see Schema design.
Change Stakeholder	Yes	People who have a stake in the schema change. All specified stakeholders can view ticket details and help move the development and approval process forward.

Create a physical table

You can create tables through the GUI or by importing SQL statements.

Option A: Use the GUI

Click Create Physical Table.
Enter the basic table information, then configure the fields and indexes.
Click Save.

Option B: Import SQL statements

Click Import SQL Statements.

In the dialog box, enter your CREATE TABLE or ALTER TABLE statements and click OK. For example, the following statement creates 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';

On the Create Physical Table: data_modify tab, confirm the table information and schema, then click Save.

Note

You can also create a physical table from the SQLConsole tab. For details, see the "Create a table" section of Manage a database on the SQLConsole tab.

Review the validation result

After you save, the Precheck Result message appears. Review the result and take one of the following actions:

Result	Action
Passed	Click Confirm Changes and Submit to Save.
Warning	Click Ignore the warning and continue to save. to proceed, or click Disable, fix the table schema to comply with R&D standards, and click Save again.
Error	Review the error cause, click Disable, fix the table schema to comply with R&D standards, and click Save again.

Note

DMS validates your table creation or modification SQL against the R&D standards configured in the default security rules, and reports any errors or warnings.
DMS provides nearly 40 R&D standards for creating tables and modifying fields and indexes. You can configure these standards in your security rules.

After the validation passes, you are taken to the Project Homepage tab. On the Tables Changed in Project tab, you can view, modify, or delete the tables you created or changed.

Apply changes to the base database

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

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

Note

Verify the changes on the SQLConsole tab of the base database to confirm they match your expectations.
By default, security rules require ticket-based approval for schema changes. You can customize these rules. For example, you can skip approval for development databases, or specify which users can approve changes to production databases.

Promote changes to production

Click Go to Next Node. In the confirmation message, click Go to Next Node again.
On the Project Homepage tab, click Perform Changes to Target Database.
In the Perform Changes to Target Database panel, select the destination database, configure the Execution Strategy, and click Submit.
Note
Click Add Multiple Databases to apply the script to multiple production databases at once.
After the change is approved, DMS automatically applies the schema changes to the production database. Verify the changes on the SQLConsole tab of the production database.
Click Go to Next Node. In the confirmation message, click Go to Next Node again.

After the ticket is closed, click any step in the workflow to review the change and publishing records.