Skip to content

Table Schema Rules

Table Schema Rules

For the field of expressing true or false, use is_xxx. In MySQL, the data type is unsigned tinyint. In oracle, the data type is number(1). In PostgreSQL, the data type is smallint. (1 means true, 0 means false) - [Recommend]

💡for example: express logically deleted fields using is_deleted

Table name and variables use lower case, underline or number - [Mandatory]

👎 GetterAdmin, taskConfig, level_3_name

👍 getter_admin, task_config, level3_name

Table name should not use plural countable nouns - [Mandatory]

💡 The table name should only represent the entity content in the table, not the number of entities.

Not use reserved words - [Mandatory]

Naming conventions such as unique index and general index - [Mandatory]

💡 primary key: pk_xxx; unique key: uk_xxx; index: idx_xxx

use decimal, no float or double - [Recommend]

💡 When storing float and double, there is a problem of accuracy loss, and it is likely to get incorrect results when comparing values. If the stored data range exceeds the decimal range, it is recommended to split the data into integers and decimals and store them separately.

Use char to store string with specific length - [Mandatory]

Use varchar to store mutable string. The length should less than 5000 - [Mandatory]

💡 varchar is a variable length string and does not pre allocate space.

Three fields necessary for table: id, gmt_create, gmt_modified - [Mandatory]

💡 id must be the primary key, the type must be unsigned bigint, and the step size must be 1. The type of gmt_create, gmt_modified is datetime. Column name can be different words, but each table MUST has those three factors.

Define table name as [table_business_name]_[table_purpose] - [Recommend]

Try to define database name same with the application name - [Recommend]

Update column comments once column meaning is changed or new possible status values are added - [Mandatory]

Fields allow appropriate redundancy to improve performance, but data synchronization must be considered - [Recommend]

💡 Redundant fields should follow:

  1. Fields that are not frequently modified
  2. It is not a varchar extra long field, and it cannot be a text field

💡 If it is expected that the data volume will not reach this level for a long time(3 years), please do not divide the database and table when creating the table.

Appropriate char column length not only saves database and index storing space, but also improves query efficiency - [Recommend]