Shulou(Shulou.com)05/31 Report--

What this article shares with you is about the naming conventions of the database, which the editor thinks is quite practical, so I share it with you to learn. I hope you can get something after reading this article. Let's take a look at it with the editor.

Database naming convention

It is composed of 26 English letters (case-sensitive) and 0-9 natural numbers (often not needed) plus an underscore, the naming is concise and clear, multiple words are separated by an underscore'_', and a business project has a database. Multiple business projects should be careful to use the same database.

Database table naming specification

Data table naming convention

(1) it is composed of 26 English letters (case-sensitive) and 0-9 natural numbers (often not needed) plus an underscore'_'. The naming is concise and clear, and multiple words are separated by an underscore'_'.

(2) all lowercase naming, uppercase is prohibited.

(3) it is forbidden to use database keywords, such as name,time,datetime,password, etc.

(4) the table name should not be too long (usually no more than three English words).

(5) the name of the table generally uses nouns or verb-object phrases.

(6) to represent the name in the singular, for example, using employee instead of employees.

The name of the parts list is: name of the main table + character dtl (detail abbreviation)

For example, if the name of the purchase order is: po_order, then the schedule of the purchase order is: po_orderdtl.

(7) the table must fill in the description information (when using SQL statement to create the table).

Table design

(1) the table must define the primary key. The default is ID, and the integer is self-increasing. If you do not use the default design, you must consult DBA for design evaluation.

(2) the ID field is used as the self-increasing primary key. In general, all tables must have id,id as the primary key, the type is bigintunsigned, and the step size is 1 when a single table is self-increasing. In general, the primary key id has nothing to do with the business.

(3) it is strongly recommended not to use foreign keys, and the integrity of the data is guaranteed by the program.

(4) for the same column in multiple tables, the column definition must be consistent.

(5) domestic tables use InnoDB by default, table character sets use gbk by default, and international tables use utf8 tables by default.

(6) generally speaking, each table has four fields create_id,create_time,update_id,update_time, in which create_id indicates the creator id,create_time indicates the creation time, and update_id indicates the update id,update_time indicates the update time. The purpose of these four fields is to track the source and modification of the data.

(7) if the amount of data in a single table exceeds 500w or the data capacity exceeds 10G in one to two years, a sub-table should be considered, and historical data migration or application deletion of historical data should be considered in advance.

(8) the size of a single record is prohibited from exceeding 8k. On the one hand, there are not too many fields, and some can have hundreds or even hundreds of fields. On the other hand, the content of the field is not easy to be too large. Such super-long content as the content of the article needs to be stored in another table separately.

(9) Log data is not recommended to be stored on MySQL. Hbase or OB is preferred. If storage is needed, please refer to DBA for evaluation and use compressed table storage.

(10) in order to improve the query efficiency, there can be appropriate data redundancy, attention is appropriate.

Database field naming specification

Field naming convention

(1) it is composed of 26 English letters (case-sensitive) and 0-9 natural numbers (often not needed) plus an underscore'_'. The naming is concise and clear, and multiple words are separated by an underscore'_'.

(2) all lowercase naming, uppercase is prohibited.

(3) description information must be filled in the field.

(4) it is forbidden to use database keywords, such as name,time,datetimepassword, etc.

(5) Field names usually use nouns or verb-object phrases.

(6) the name of the adopted field must be easy to understand and generally no more than three English words.

(7) do not repeat the name of the table when naming the columns of the table.

For example, avoid using a field named employee_lastname in a table named employe.

(8) do not include data types in the names of columns.

(9) the full name is used for field naming, and abbreviations are prohibited.

Field name naming example

(1) use underscore for separation to improve readability.

For example, user_name,user_id,is_friend,is_good.

(2) use the full name to improve readability.

For example, uid is changed to user_id,pid and changed to person_id.

Field design

(1) the indexed column of the table must be defined as notnull and set the default value.

(2) the use of float or double types is prohibited. It is recommended to use decimal or int instead.

(3) it is forbidden to use blob and text types to keep large text, files and pictures. It is recommended to store them in other ways. MySQL only saves pointer information.

(4) the use of the varchar type as the primary key is prohibited.

These are the database naming conventions, and the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.