Database Basic Design Specification:

Database Design Specification: summary
All database object names must be split with lowercase letters and underlined

All database object names prohibit the use of MySQL reserved keywords

The name of the database object is to be known, and it is best not to exceed 32 characters

Database Design Specification:

The temp library must be prefixed with TMP and suffixed with a date
Backup library, the backup table must be prefixed with Bak and suffixed with a date

-------
All column names and column types that store the same data must be consistent
=======================================
Database clearly specification summary

All database object names must be lowercase
Name to be able to recognize the meaning of the name, prohibit the use of MySQL reserved keywords
The staging table starts with TMP_, the backup table starts with Bak_, and ends with a timestamp
All column names and column types that store the same data must be consistent

Database Basic Design Specification:

All tables must use the InnoDB storage engine
5.6 The default engine after
Support transactions, row-level locks, better recoverability, better performance under high concurrency
The database and the table's character set agree to use UTF8
Uniform character set avoids garbled characters due to character set conversion
UTF8 Character Set Kanji dot 3 bytes in MySQL, ASCII code occupies 1 bytes

Add comments to all tables and fields
Add notes to tables and columns using comment clauses
Maintenance of data dictionaries from the outset

Try to control the size of the single-table data volume, the proposed control within 5 million
5 million is not a MySQL database limitation
Can use historical data archiving, sub-database and other means to control the size of the data volume
How much data can msql store?
This limitation depends on the storage settings and the file system:

Use MySQL partition table sparingly
Because partitioned tables are physically represented as multiple files, which are logically represented as a table
Careful selection of partition key, cross-partition query efficiency may be lower
(Big table) recommend the use of physical sub-table to manage big data
Try to separate the hot and cold data to reduce the width of the table
Reduce disk IO to ensure memory cache hit rate for hot data
Avoid reading useless cold data with more efficient use of the cache

Columns that are often used together are placed in a table

Prohibit establishment of reserved fields in tables
Naming of reserved fields is difficult to recognize
Reserved field cannot confirm the stored data type, so the appropriate type cannot be selected
The table is locked for modifications to the reserved field type

(The cost of modifying a field is greater than the new field)
Prohibit storing binary data such as pictures, files, etc. in the database
Prohibit on-line database stress testing (generates a lot of junk data)
Prohibit direct connection to production environment database from development environment, test environment

====================

Summary of design specification for database progress

All tables must use the InnoDB storage engine

All tables and fields need to be annotated and use the UTF8 character set

Try to control the size of the single-table data and separate the hot and cold data

Prohibit use of reserved fields and storing binary data in tables

Index Design Specification

Indexes are very important to the query performance of the database.

1. Limit the number of indexes on each table, it is recommended to index no more than 5 sheets
The index is not the more the better! Indexing can be more efficient and less efficient
Prevent individual indexes from being created for each column in a table

Database Basic Design Specification: