Data Management:Schema design

Prerequisites

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 MySQL databases from other sources

  • OceanBase

  • Oracle

• A database instance managed in Secure Collaboration mode. See View the control mode of an instance.

• A database whose environment type meets the environment requirement in the security rules for schema change. See Change the environment type of an instance.

Schema design supports both physical databases and logical databases. This guide uses a physical database.

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.

   In simple mode, hover over the icon in the upper-left corner and choose All Features > Database Development > Schema Change > Schema Design.

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

4. Set the ticket parameters and click Submit.

   Parameter	Required	Description
   Project Name	Yes	A name that identifies the project.
   Project description	Yes	The business background of the project, such as its purpose or objective. A clear description reduces back-and-forth communication.
   Change Base Database	Yes	The database for schema design. The database's environment type must conform to the R&D standards in the security rules. In this example, poc_dev is used as the change base database. For more information, see Schema design.
   Security Rules	No	The security rules to apply. DMS selects these automatically after you specify a database.
   Associated Iterations	No	The iteration to associate with the project. Click Select/Change Iteration, find the iteration, and click Associate in the Actions column.
   Change Stakeholder	Yes	Users who can view the ticket and assist in the development and approval processes. DMS administrators and database administrators (DBAs) can always view ticket details; other users cannot.

Step 2: Create a physical table

Create a table using either the GUI or by importing SQL statements. You can also create a physical table on the SQLConsole tab. The following steps use SQL.

Option 1: Use the GUI

1. Click Create Physical Table.

2. Enter the basic table information and configure fields and indexes.

3. Click Save.

Option 2: Import SQL statements

1. Click Import SQL Statements.

2. Enter a CREATE TABLE or ALTER TABLE statement and click OK. For example:

   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 schema and click Save.

After you save, DMS validates the statement against the R&D standards in the default security rules and displays a precheck result.

DMS provides nearly 40 R&D standards for table creation and field or index modification. Configure these standards in the security rules as needed.

After the precheck passes, the Project Homepage tab opens. On the Tables Changed in Project tab, review the tables you modified or created and make further changes as needed.

Step 3: Apply schema changes to the development database

1. Click Perform Changes to Base Database.

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

After the schema change is approved, DMS applies it to the base database automatically. To verify the result, open the SQLConsole tab for the base database.

By default, schema changes require ticket submission and approval. With custom security rules, you can skip approval for development databases or designate specific approvers for production databases.

Step 4: Apply schema changes to the production database

1. Click Perform Changes to Target Database.

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

   To apply the same script to multiple production databases at once, click Add Multiple Databases.

3. After the schema change is approved, DMS applies it to the production database automatically. Verify the result on the SQLConsole tab.

4. Click Go to Next Node. In the confirmation message, click Go to Next Node again to close the ticket.

After the ticket closes, click any step to view its change and publishing records.

What's next

For the complete reference on schema design, including advanced configuration options, see Design schemas.