Data Management:Schema design for logical tables

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, move the pointer over the icon in the upper-left corner and choose All functions > Database Development > Schema Change > Schema Design.

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

4. Set the following parameters and click Submit.

   Parameter	Description
   Change Base Database	The logical database for schema design. Select a database in Security Collaboration mode. The environment type must conform to the R&D standards in the database's security rules. For details, see Schema design.
   Change Stakeholder	Stakeholders who can view the ticket details and assist with development and approvals. DMS administrators and database administrators (DBAs) can always view ticket details; other users can view only if added here.

Step 2: Define the logical table schema

Define the logical table using the GUI or SQL statements.

Use the GUI

1. Click Create Logical Table.

2. Set the basic information, columns, and indexes using the Basic Info, Column Info, and Index Info tabs.

3. Click Partition Table Topology and enter a shard expression in the Logical Table Expression field. For the full expression syntax, see Expressions of logical tables.

   Warning

   Modifying a shard expression deletes the original table and creates a new one. This action cannot be undone.

4. Click Calculate Table Topology Distribution to preview the shard names and their physical distribution across databases. The preview shows each shard name and the physical database it maps to. If the result does not match your expectations, adjust the expression and recalculate.

5. Click Save.

Use SQL statements

1. Click Import SQL Statements.

2. Enter a CREATE TABLE or ALTER TABLE statement and click OK. The following example creates two tables, orders_logic_[05] and orders_logic_[06], using a range expression:

   CREATE TABLE `orders_logic_[05-06]` (
       `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
       `gmt_create` datetime NOT NULL COMMENT 'create time',
       `gmt_modified` datetime NOT NULL COMMENT 'modify time',
       PRIMARY KEY (`id`)
   ) DEFAULT CHARACTER SET=utf8 COMMENT='Create Logical Table';

3. On the Create Logical Table: orders_logic_[05-06] tab, review the table information and schema, then click Save.

   Avoid modifying the shard expressions of existing tables.

Step 3: Review the precheck result

After saving, DMS runs a precheck against nearly 40 R&D standards for table creation, field modifications, and index changes. The schema change enters the approval stage only after the precheck passes.

After the precheck passes, DMS navigates you to the Project Homepage tab. On the Tables Changed in Project tab, review, modify, or delete tables as needed before proceeding to approval.

Step 4: Apply changes to the base database

1. Click Perform Changes to Base Database.

2. In the Perform Changes to Base Database panel, set Execution Strategy, Canary Mode, and Canary Action, then click Submit.

After approval, DMS automatically applies the schema changes to the base database. Track progress on the Change Execution History tab—status is reported per physical database. To verify the result, open the SQLConsole tab for the base database.

By default, 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 5: Promote to the production database

1. Click Go to Next Node, then confirm in the dialog.

2. On the Project Homepage tab, click Perform Changes to Target Database.

3. In the Perform Changes to Target Database panel, select the destination database and click Submit.

   To apply the schema changes to multiple production databases at once, click Add Multiple Databases.

4. After approval, DMS automatically applies the changes to the production database. Verify the result on the SQLConsole tab for the production database.

5. Click Go to Next Node, then confirm in the dialog.

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