MySQL database naming specification and conventions

Source: Internet
Author: User
Tags add time

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.