Some principles of the database and table specifications

In order to standardize database operations, unify team communication methods, facilitate subsequent project maintenance, improve product code quality, and guide software developers to write concise, maintainable, reliable, testable, efficient, and portable SQL, refer to company conditions and industry database specification, so this specification was written and codified.

Term

Principle
: The guiding ideology that must be adhered to when programming.
Rules
: conventions that must be followed when programming.
Suggestions
: Conventions that must be considered when programming.

Explanation: Provide necessary explanation of this principle/rule/recommendation.

Examples: Give examples of this principle/rule/suggestion from both positive and negative aspects. Further reading materials: Reference materials for further reading.

Specification

Principle: Columns in timestamp format are not allowed

Explanation:

Timestrap has three major problems: performance problems, implicit problems, and time ranges.

Example:

created_time` timestrap NOT NULL COMMENT 'Created Time',

Principle: Coding format and Collate rules for databases & tables

Explanation:

Encoding format: utf8mb4

Collate rule: utf8mb4_unicode_ci

Example:

Database

CREATE DATABASE `t_name`  DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ;

Table

CREATE TABLE `d_name`.`t_name` (
  `id` bigint(20) NOT NULL COMMENT 'primary id',
  `is_deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'logical deletion, 0: not deleted, 1: deleted',
  `created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'created time',
  `updated_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'updated time',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci  COMMENT='table comment';

Principle: Tables, columns, fields, and indexes must have relevant comments

Explanation:

Table COMMENT ‘xxx’, column COMMENT ‘xxx’, index COMMENT ‘xxx’

Normal indexes: non-unique indexes are named using IDX_field_name

Unique indexes: Unique indexes are named using UNI_field_name

Example:

CREATE TABLE `d_name`.`t_name` (
  `id` bigint(20) NOT NULL COMMENT 'primary id',
  `application_id` bigint(20) NOT NULL COMMENT 'application id',
  `is_deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'logical deletion, 0: not deleted, 1: deleted',
  `created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'created time',
  `updated_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'updated time',
  PRIMARY KEY (`id`),
  KEY `IDX_application_id-is_deleted` (`application_id`,`is_deleted`) COMMENT 'logical delete index'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci  COMMENT='table comment';

Principle: Add created_time and updated_time fields to each table

Explanation:

`created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'created time',
`updated_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'updated time',

Example:

CREATE TABLE `d_name`.`t_name` (
  `id` bigint(20) NOT NULL COMMENT 'primary id',
  `created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'created time',
  `updated_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'updated time',
  PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci  COMMENT='table comment';

Principle: Do not insert position markers when adding new columns in the data table

Explanation:

FIRST:Indicates that the addition is before a column

AFTER:Indicates that the addition is after a column

Example:

Error

ALTER TABLE `d_name`.`t_name` 
ADD COLUMN `is_deleted` VARCHAR(45) NULL AFTER `updated_by`  COMMENT 'logical deletion, 0: not deleted, 1: deleted';

Right

ALTER TABLE `d_name`.`t_name` 
ADD COLUMN `is_deleted` VARCHAR(45) NULL  COMMENT 'logical deletion, 0: not deleted, 1: deleted';

Principle: Updating database records requires backup statements

Explanation:

Which conditional data columns are updated, which conditional columns are backed up

The data of which conditions are updated, the data of which conditions are backed up

Example:

For example, if I update the column created_time based on id (1, 2, 3), then only the created_time column of id (1, 2, 3) needs to be backed up.

UPDATE `d_name`.`t_name` SET created_time=NOW() WHERE id in(1,2,3);

SELECT id,created_time  FROM `d_name`.`t_name` WHERE id in(1,2,3) INTO OUTFILE '/tmp/mysql/d_name-t_name-YYYYMMDD-num.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

Principle: It is not allowed to delete fields that already exist in the table

Explanation:

Cannot drop column using DROP COLUMN

Example:

ALTER TABLE `d_name`.`t_name` 
DROP COLUMN `version`;

Principle: When making changes, submit attachments uniformly and separate DDL and DML statements into two files

Explanation:

DDL and DML statements are separated into two files

file name: library name-table name-change date (YYYYMMDD-1)-ddl.SQL, all are .SQL suffix

Unified encoding UTF-8, write the execution order in the remarks

The operations of adding, deleting and modifying must first backup the statement, refer to: (update database records must have backup statements)

Example:

Name:d_name-t_name-20200820-1-ddl.SQL

ALTER TABLE `d_name`.`t_name` 
ADD COLUMN `is_deleted` VARCHAR(45) NULL  COMMENT 'logical deletion, 0: not deleted, 1: deleted';

Name:d_name-t_name-20200820-1-dml.SQL

DELETE FROM `d_name`.`t_name` WHERE id= 1;

Principle: Must have a unique primary key index and auto increment

Explanation:

`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'primary id'

Example:

CREATE TABLE `d_name`.`t_name` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'primary id',
  `created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'created time',
  `updated_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'updated time',
  PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci  COMMENT='table comment';

Principle: The fields in the table cannot have NULL values of type CHAR

Explanation:

When ROW_FORMAT=COMPACT, the NULL value of CHAR type does not occupy space

When ROW_FORMAT=REDUNDANT, the NULL value of CHAR type takes up space

VARCHAR takes no storage space in either row format

Example:

CREATE TABLE `d_name`.`t_name` (
  `id` bigint(20) NOT NULL COMMENT 'primary id',
  `application_id` bigint(20) NOT NULL COMMENT 'application id',
  `is_deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'logical deletion, 0: not deleted, 1: deleted',
  `created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'created time',
  `updated_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'updated time',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='table comment';

Principle: Do not use internal keywords as column names

Explanation:

For example:NAME,VALUE,SELECT,ALTER AND,DELETE, etc.,Refernces

Example:

Error:(delete column)

CREATE TABLE `d_name`.`t_name` (
  `id` bigint(20) NOT NULL COMMENT 'primary id',
  `application_id` bigint(20) NOT NULL COMMENT 'application id',
  `delete` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'logical deletion, 0: not deleted, 1: deleted',
  `created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'created time',
  `updated_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'updated time',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='table comment';

Right:(delete column)

CREATE TABLE `d_name`.`t_name` (
  `id` bigint(20) NOT NULL COMMENT 'primary id',
  `application_id` bigint(20) NOT NULL COMMENT 'application id',
  `is_deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'logical deletion, 0: not deleted, 1: deleted',
  `created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'created time',
  `updated_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'updated time',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='table comment';

Principle: Do not delete any column and table

Explanation:

For example:DROP table

For example:Alter table table_name drop column column_name;

Example:

Error:(Drop table & column)

DROP table tablea;
Alter table tablea drop column col;

Principle: Do not change any existing table names and column name

Explanation:

For example: ALTER TABLE old_table RENAME AS new_table;

For example: ALTER TABLE old_table change old_column new_columb column_type;

Example:

Error

ALTER TABLE `old_table` RENAME AS `new_table`;
alter table `old_table` change `old_columnname` `new_columnname` varchar(20);

Example of backup table:

CREATE TABLE `test`.`user1` (
  `id` int(11) NOT NULL,
  `names` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='table user';

--Contains all data and structures (but not primary keys, indexes, etc.)
CREATE TABLE `test`.`user2` AS  
(  
    SELECT *  
    FROM test.user1
);

-- Contains all structure and primary key information (but not data, etc.)
CREATE TABLE `test`.`user3` LIKE `test`.`user1`; 

-- Insert original data
INSERT INTO `test`.`user3`  SELECT * FROM `test`.`user1`;

-- Contains all data and structures (but not primary keys, indexes, etc.)
CREATE TABLE `test`.`user4` SELECT * FROM `test`.`user1`;

Principle: Delete row records in the table, the product does not specifically say that physical deletion is required, and logical identification is required to delete

Explanation:

`is_deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'logical deletion, 0: not deleted, 1: deleted',

Example:

CREATE TABLE `d_name`.`t_name` (
  `id` bigint(20) NOT NULL COMMENT 'primary id',
  `application_id` bigint(20) NOT NULL COMMENT 'application id',
  `is_deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'logical deletion, 0: not deleted, 1: deleted',
  `created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'created time',
  `updated_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'updated time',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci  COMMENT='table comment';

Anig Kus

Technologist | Author | Interested in Distributed Technology and Cloud Native Fields

Some principles of the database and table specifications

Term

Specification

Principle: Columns in timestamp format are not allowed

Principle: Coding format and Collate rules for databases & tables

Principle: Tables, columns, fields, and indexes must have relevant comments

Principle: Add created_time and updated_time fields to each table

Principle: Do not insert position markers when adding new columns in the data table

Principle: Updating database records requires backup statements

Principle: It is not allowed to delete fields that already exist in the table

Principle: When making changes, submit attachments uniformly and separate DDL and DML statements into two files

Principle: Must have a unique primary key index and auto increment

Principle: The fields in the table cannot have NULL values of type CHAR

Principle: Do not use internal keywords as column names

Principle: Do not delete any column and table

Principle: Do not change any existing table names and column name

Principle: Delete row records in the table, the product does not specifically say that physical deletion is required, and logical identification is required to delete

back