As a back-end developer, we often need to design database tables. I have compiled 21 empirical guidelines for designing MySQL tables and shared them with you, and everyone will definitely be helpful after reading them.

1. Naming conventions

Database table names, field names, index names, etc. all need naming conventions, high readability (generally required in English), so that others can see the name, you know what this field means.

For example, the account number field of a table, but the following:

Positive example:

Primary key index name pk_ field name; Unique index name uk_ field name; Normal index names are idx_ field names.

2. Select the appropriate field type

Choose a field type with little storage space as much as possible, just like a numeric type, starting from tinyint, smallint, int, bigint from left to right

For decimal types such as amounts, select decimal and disable the use of float and double.

If the stored strings are almost equal in length, use the char fixed-length string type.

The varchar is a variable-length string that does not pre-allocate storage space and does not exceed 5000 in length.

If the stored value is too large, it is recommended that the field type be changed to text, and a separate table is extracted and corresponded to with a primary key.

In the same table, the length of all varchar fields can add up to no greater than 65535. If you have such a requirement, use the TEXT/LONGTEXT type.

3. The primary key design should be reasonable

4. Select the appropriate field length

5. Prioritize tombstones over physical deletions

6. Each table needs to be added

These are several common fields

Tables must be general, or have these fields:

id: primary key, a table must have a primary key, must

create_time: Creation time, required

modifed_time: Modified at the time, must be updated, when the record is updated, it needs to be updated

version : The version number of the data record, used for optimistic locks, not required

remark : Data record remarks, not required

modified_by: Modification person, not required

7. A table should not have too many fields

8. Define fields with not null whenever possible

First, NOT NULL prevents null pointer problems.

Secondly, the NULL value store also requires additional space, which also causes more complex comparison operations and makes it difficult for the optimizer to optimize SQL.

A NULL value may cause the index to be invalidated

9. When designing the table, evaluate which fields

Indexing is required

Do not build too many indexes, generally do not exceed 5 indexes per table. Because too many indexes are created, the writing speed is reduced.

Fields that are not highly distinguished cannot be indexed, such as gender, etc

After the index is created, it is still necessary to pay attention to avoid the failure of the index, such as the use of mysql’s built-in function, which will cause the index to fail

10. Do not need to strictly adhere to 3NF,

Reduce table associations through business field redundancy

The first normal form: for the atomicity of the property, it is required that the property be atomical and can no longer be decomposed;

The second paradigm: for the uniqueness of the record, it is required that the record has a unique identification, that is, the uniqueness of the entity, that is, there is no partial dependency;

We design the relationship between tables and their fields as much as possible to meet the third normal form. But sometimes, there is a lot of redundancy to improve efficiency. For example, the following table

The product name

Product model

unit price


The total amount

cell phone





The above is a basic table for storing product information. The existence of this field of total amount indicates that the design of the table does not meet the third normal form, because the total amount can be obtained from the unit price* quantity, indicating that the total amount is a redundant field. However, adding the redundant field of total amount can improve the speed of query statistics, which is the practice of exchanging space for time.

11. Avoid using MySQL reserved words

12. Do not engage in foreign key association,

Generally in code maintenance

13. The INNODB storage engine is generally selected

Let’s review the comparison and difference between the three storage engines of MySQL:

14. Choose the appropriate unified character set.

utf8: Support Chinese and English mixed scenes, international pass, 3 bytes length

utf8mb4: Fully compatible with utf8, 4 bytes in length, generally used to store emoji emoticons.

GBK : Chinese supported, but not for international character sets, 2 bytes in length

15. If your database fields are of enumeration type,

Need to be clearly commented in the comment

If you design a database field that is an enumeration type, you need to annotate the meaning of each enumeration after the comment for ease of maintenance

16. Time type selection

Date : Represents the date value, in the format yyyy-mm-dd, ranging from 1000-01-01 to 9999-12-31, 3 bytes

Time : Represents the time value, in the format hh:mm:ss, in the range -838:59:59 to 838:59:59, 3 bytes

datetime: Represents a datetime value in the format yyyy-mm-dd hh:mm:ss, ranging from 1000-01-01 00:00:00 to 9999-12-31 23:59:59”’, 8 bytes, independent of time zone

timestamp: Represents a timestamp value in the format yyyymmddhhmmss, ranging from 1970-01-01 00:00:01 to 2038-01-19 03:14:07, 4 bytes, depending on the time zone

17. Stored procedure is not recommended

(including stored procedures, triggers) 

Cascading changes can be implemented through related tables in the database.

Real-time monitoring of changes to a field in a table requires appropriate processing.

For example, you can generate numbers for certain transactions.

18. Design of the 1:N relationship

19. Large fields

20. Consider whether you need to sub-database and table sub-tables

Transaction issues

Cross-library association

Sort the problem

Paging issues

21. Some optimization experience with sqL writing

Query SQL try not to use select *, but select specific fields

If you know that there is only one query result or only one record maximum/minimum, we recommend that you use limit 1

You should try to avoid using or to join conditions in the Where clause

– EOF –

How to achieve database read consistency

Dig three feet into the Redis and MySQL data consistency issues

Got a harvest after reading this article? Please forward and share it with more people

Follow “ImportNew” to improve your Java skills

Likes and looks are the biggest support ❤️