1. [Mandatory] Columns expressing the concept of True or False, must be named as is_xxx, whose data type should be unsigned tinyint (1 is True, 0 is False).

Note: All columns with non-negative values must be unsigned.

2. [Mandatory] Names of tables and columns must consist of lower case letters, digits or underscores. Names starting with digits and names which contain only digits (no other characters) in between two underscores are not allowed. Columns should be named cautiously, as it is costly to change column names and cannot be released in pre-release environment.

Positive example: getter_admin, task_config, level3_name

Counter example: GetterAdmin, taskConfig, level_3_name

3. [Mandatory] Plural nouns are not allowed as table names.

4. [Mandatory] Keyword, such as desc, range, match, delayed, etc., should not be used. It can be referenced from MySQL official document.

5. [Mandatory] The name of primary key index should be prefixed with pk_, followed by column name; Unique index should be named by prefixing its column name with uk_; And normal index should be formatted as idx_[column_name].

Note: pk means primary key, uk means unique key, and idx is short for index.

6. [Mandatory] Decimals should be typed as decimal. float and double are not allowed.

Note: It may have precision loss when float and double numbers are stored, which in turn may lead to incorrect data comparison result. It is recommended to store integral and fractional parts separately when data range to be stored is beyond the range covered by decimal type.

7. [Mandatory] Use char if lengths of information to be stored in that column are almost the same.

8. [Mandatory] The length of varchar should not exceed 5000, otherwise it should be defined as text. It is better to store them in a separate table in order to avoid its effect on indexing efficiency of other columns.

9. [Mandatory] A table must include three columns as following: id, gmt_create and gmt_modified.

Note: id is the primary key, which is unsigned bigint and self-incrementing with step length of 1. The type of gmt_create and gmt_modified should be DATE_TIME.

10. [Recommended] It is recommended to define table name as [tablebusiness_name][table_purpose].

Positive example: tiger_task / tiger_reader / mpp_config

11. [Recommended] Try to define database name same with the application name.

12. [Recommended] Update column comments once column meaning is changed or new possible status values are added.

13. [Recommended] Some appropriate columns may be stored in multiple tables redundantly to improve search performance, but consistency must be concerned. Redundant columns should not be:
  1) Columns with frequent modification.
  2) Columns typed with very long varchar or text.

Positive example: Product category names are short, frequently used and with almost never changing/fixed values. They may be stored redundantly in relevant tables to avoid joined queries.

14. [Recommended] Database sharding may only be recommended when there are more than 5 million rows in a single table or table capacity exceeds 2GB.

Note: Please do not shard during table creation if anticipated data quantity is not to reach this grade.

15. [For Reference] Appropriate char column length not only saves database and index storing space, but also improves query efficiency.

Positive example: Unsigned types could avoid storing negative values mistakenly, but also may cover bigger data representative range.