MySQL database naming conventions

I. [operational specifications]
1. If there is no remark, the first id field in the table must be the primary key and automatically grow;
2. If there is no remark, use the UNSIGNED attribute for numeric fields;
3. If there is no remark, order_id is used by default in the program in descending order;
4. If there is no remark, all fields are set not null and the default value is set;
5. If there is no remark, a default value must be set for all Boolean fields, such as is_hot and is_deleted, and set to 0;
6. A default value must be set for all numeric fields and set to 0;
7. For varchar type field processing, please verify the user input, do not exceed its preset length;
8. When creating a table, write the Chinese name and attribute remarks of the fields in the data dictionary into the remarks of the data table ("PK, automatic growth" do not need to be written );
9. If no description is provided, the innodb engine is used for table creation;

Ii. Common table name conventions]
0. Note: The table prefix is abbreviated as the project name. Therefore, the table names are in lower case, separated by underscores (_), and words are in the singular form.
1. user-user
2. category-category
3. goods-this is used to name all items that can be traded on a website, such as commodities and products.
4. good_gallery-album of items
5. good_cate-item category. Except for the table name, all words in other categories are abbreviated as cate.
4. attr-attributes
5. Articles-articles, news, and help centers are generally named after articles.
6. cart-shopping cart
7. feedback-user feedback
8. order-order
9. site_nav-including page header and page tail navigation
10. site_config-system configuration table
11. admin-Background user [RBAC standard table]
12. role-Background User role [RBAC standard table]
13. access-Background operation permission, equivalent to action [RBAC standard table]
14. role_admin-Role of the background user [RBAC standard table]
15. access_role-permissions of the background role [RBAC standard table]
16. To be continued

Iii. Common column name conventions]
1. Table name_id-usually used as a foreign key name
2. cid-special number with metadata to facilitate associated queries. You can think of it as a category (level) number. For example, when a product is classified, it is often categorized into sub-categories, and only the id of sub-categories is recorded in the corresponding fields, if you need to know which category the product belongs to, You need to query the information of the primary category through the sub-category information. This is troublesome, And the cid field is to solve this problem. Generally, dozens of types of websites are sufficient. Therefore, if the cid of a primary category is 11, the cid of the subcategory starts from 1101, when processing, you only need to extract the first two digits to know which category the product belongs.
3. add_time-add time, shelving time, etc.
4. last_time-last operation time, such as logon and modification records
5. expire_time-expiration time
6. name-product name, seller name, etc. Do not mix with title. title is only used for article title, title, etc.
7. price-price
8. thumb-use this name for all window charts on the list page.
9. All image addresses in the image_src-album should be named after this. Do not use img, image, img_url, thumb_url, etc.
10. head_thumb-user profile picture, although a little long, must follow. Do not
11. image_alt-alt attribute of the image in the album
12. desc-description and description, such as goods_desc. Do not use goods_txt.
13. details-details, article content, etc.
14. order_id-sort
15. telephone-landline number
16. mobile phone number
17. phone-use phone name when phone and landline are not differentiated
18. address-address. Do not use addr for the combination, such as mac address or mac_addr.
19. zipcode-zip code
20. region-region, large area, such as Hangzhou City and Wenzhou City
21. area-area, small, such as Shangcheng district and jianggan District
22. avg_cost-per capita consumption
23. To be continued

Iv. [data table field design example]

Category Table (T_category)

Field name	Column name	Type	Attribute remarks	Description
Serial number	Id	Int (10)	PK, automatic Growth
Special Number	Cid	Varchar (4)		The first primary category is 11, the first subcategory is 1101, and so on. Only secondary categories are supported.
Name	Name	Varchar (10)		Enter no more than 10 words on the page
Parent category	Pid	Int (10)
Statistics	Count	Int (10)
Hot?	Is_hot	Tinyint (1)
Homepage display	Is_index	Tinyint (1)
Sort	Order_id	Int (10)