Mysql Database naming specification and convention _mysql

First, "Operating norms"
1. If no notes, the first ID field in the table must be the primary key and automatically grow;
2. If no notes, the field of the numeric type uses the unsigned property;
3. If no notes, sort field order_id in the program by default in descending order;
4. If no notes, all fields are set to not NULL and the default values are set;
5. If no notes, all the Boolean fields, such as Is_hot, is_deleted, must set 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 varchar Type field of the program processing, please verify the user input, do not exceed its preset length;
8. When the table is set up, the Chinese name and attribute notes of the fields in the data dictionary are written to the notes in the datasheet ("PK, auto Grow" without writing);
9. If no explanation, the InnoDB engine is used in the construction of the table;

Second, "Common table name Convention"
0. Note: The table prefix is abbreviated with the project name, so the table names are lowercase, the words are separated by an underscore, and the words are in singular form.
1. user– Users
2. category– classification
3. goods– goods, products and so on all the items that can be traded website is named
4. Albums for good_gallery– items
5. The classification of good_cate– items, in addition to the table name alone, the classification of words in other places are all abbreviated Cate
4. attr– Properties
5. article– articles, news, help Center, etc. in the form of articles, generally used this name
6. cart– Shopping Cart
7. feedback– User Feedback
8. order– Order
9. site_nav– including page header and footer navigation
site_config– System Configuration Table
admin– background user "RBAC standard table"
role– background User Role "RBAC standard table"
access– background operation Rights, equivalent to action "RBAC standard table"
role_admin– background user corresponding role "RBAC standard table"
access_role– background role corresponding to the rights "RBAC standard table"
16. To be Continued

three, "Common column name Convention"
1. Table name _id– is often used as a foreign key name
2. cid– special number, with metadata, easy to associate query, you can understand it as a category (hierarchy) number. For example, when a product is categorized, it often needs to be categorized into subcategories, the corresponding field is also generally only record the ID of the subcategory, then if you need to know what the main classification of the product, you need to query the sub-classification information to the main classification information, this is more troublesome, CID field is to solve this problem. The general site dozens of categories must be enough, so this assumes that a major classification of CID is 11, then the sub-classification of CID from 1101 start numbering, processing only need to intercept the first two digits to know the product belongs to which 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– commodity name, merchant name, etc., do not mix with title , title is only used for article title, title, etc.
7. price– Price
8. thumb– as long as it is a window diagram in the list page, use this name
9. image_src– Photo address in the album is named, do not appear all kinds of img,image, Img_url,thumb_url and other
head_thumb– user head, although a bit long, be sure to comply. Do not appear in the above situation
image_alt– the alt attribute of the picture in the photo album
desc– Description, Introduction, such as GOODS_DESC, do not appear goods_txt such
details– details, article contents, etc.
order_id– sort
telephone– Landline number
mobile– mobile phone number
phone– when you do not distinguish between mobile phones and landline, use phone name
address– address, separate out Do not use addr abbreviations, combinations appear with abbreviations, such as MAC address, mac_addr
zipcode– zip
region– area, a large area, such as the record of Hangzhou, Wenzhou, etc.
area– area, small, such as Shangcheng District, Jianggan District, etc.
avg_cost– per capita consumption
23. To be continued

Example of "Data table field design"

Category Table ( t_category )

Field name	Column Name	Type	Attribute notes	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 by analogy, only the level two classification is supported
Name	Name	varchar (10)		The page should indicate the input no more than 10 words
Parent category	Pid	Int (10)
Statistical volume	Count	Int (10)
Is hot	Is_hot	tinyint (1)
Home Display	Is_index	tinyint (1)
Sort	order_id	Int (10)