MySQL database naming specification and conventions

First, "Operation Specification"
1. If no notes, the first ID field in the table must be the primary key and automatically grow;
2. If no note, the field of the numeric type use the unsigned property;
3. If no notes, the Sort field order_id in the program by default in descending order;
4. If no note, all fields are set not NULL, and the default value is set;
5. If no note, all Boolean fields, such as Is_hot, is_deleted, must be set to a default value, and set to 0;
6. All numeric type fields must be set to a default value and set to 0;
7. For the program handling of the varchar type field, verify the user input, not exceeding its preset length;
8. When the table is built, the names and attributes of the fields in the data dictionary are written to the notes in the data table ("PK, autogrow" does not have to be written);
9. If not stated, the InnoDB engine is used in the construction of the table;

second, common table name Conventions
0. Description: The table prefix is abbreviated with the project name, so the table is lowercase, the words are separated by underscores, and the words are in the singular form
1. user– user
2. category– classification
3. goods– goods, products, etc. all items that can be traded on a Web site are named with this
4. good_gallery– items of the album
5. good_cate– The classification of items, except as a table name, other places classified words are abbreviated Cate
4. attr– Property
5. article– articles, news, help centers, etc. appear in the form of articles, which are generally named with this
6. cart– Shopping Cart
7. feedback– User Feedback
8. order– Order
9. Sit e_nav– includes page header and footer navigation
. site_config– System Configuration Table
One by one. admin– background user "RBAC standard table"
. role– background User role "RBAC standard table"
. access– Operation permissions, equivalent to the action "RBAC standard table"
. role_admin– background user corresponding role "RBAC standard table"
. Permissions for access_role– background role "RBAC standard table"
16. To be continued

Three, common column name Conventions
1. Table name _id– is typically used as a foreign key name
2. cid– special number, with metadata, to facilitate the association of queries, you can interpret it as a category (level) number. For example, the product in the classification, often need to classify it into sub-classification, the corresponding field is also generally only the ID of the sub-classification, then if you need to know what the main classification of the product, it is necessary to check the sub-classification information to the main classification information, which is more troublesome, the CID field is to solve the problem. General site Dozens of categories must be enough, so here is the assumption that a main classification of the CID is 11, the sub-classification of the CID starting from 1101 number, processing only need to intercept the first two digits to know the product belongs to which the main classification.
3. add_time– add time, shelf time, etc.
4. last_time– Last operation time, such as login, modify record
5. expire_time– Expiration
6. name– Product name, business name, etc., do not mix with title , title is only used for article title, title, etc.
7. price– Price
8. thumb– as long as the window graph in the list page, all use this name
9. image_src– the picture address in the album all use this name, do not appear various img,image, Img_url,thumb_url such as
head_thumb– user avatar, although a bit long, be sure to follow. Do not appear in the above situation
one. image_alt– the alt attribute of a picture in a photo album
. desc– description, Introduction, such as GOODS_DESC, do not appear goods_txt this
. details– details, article content, etc.
order_id– sort
. telephone– landline number
. mobile– mobile phone number
. phone– when the phone and landline are not differentiated, use the phone name
. address– address, separate out Do not use the addr abbreviation, the combination appears with abbreviations, such as the MAC address, mac_addr
. zipcode– postcode
. region– area, large area, such as the record of Hangzhou, Wenzhou and so on
. area– area, small, such as Shangcheng District, Jianggan District, etc.
avg_cost– per capita consumption
23. To be continued

Iv. "Data table field Design Example"

Classification Table ( t_category )

Field name	Column Name	Type	Attribute remarks	Description
Serial number	Id	Int (10)	PK, Auto growth
Special number	Cid	varchar (4)		The first primary category is 11, the first sub-category is 1101, and so on, only two-level classification is supported
Name	Name	varchar (10)		The page must indicate the input not more than 10 words
Parent category	Pid	Int (10)
Statistic Amount	Count	Int (10)
Is hot	Is_hot	tinyint (1)
Home Display	Is_index	tinyint (1)
Sort	order_id	Int (10)

MySQL database naming specification and conventions