Standardized database naming

Source: Internet
Author: User

I. Database File naming

The data file name adopts the system name + _ + file type. For example, if the system name is ComSys, the database file is named ComSys_database.mdf. Some database files have multiple, for example, SQL Server has two, one is a database file and the other is a log file. Their files are named ComSys_database.mdf and ComSys_log.log respectively.

Ii. database object naming rules

Database objects include tables, views (queries), stored procedures (parameter queries), functions, and constraints. The object name consists of the prefix and actual name, and the length cannot exceed 30.

1. Prefix:

Use lowercase letters. For example:

Table tb

View vi

Stored Procedure sp

Function fn

2. actual name:

The actual name is used to describe the object content. It is composed of words or words. The first letter of each word is uppercase, And the other letters are lowercase. It does not start with a number or. For example:

Table User_Info

View User_List

Stored Procedure User_Delete

3. Example:

The valid object names are similar to the following:

Table tb_User_Info tb_Message_Detail

View vi_Message_List

Stored Procedure sp_Message_Add

Iii. Table naming

1) The table name consists of the prefix and actual name. The prefix uses lowercase letters tb to indicate the table.

For a working table, the table name can be prefixed with WORK _ followed by the name of the application using the table. During the naming process, you can piece together the abbreviations Based on the semantics. Note that the ORCLE field names must be in uppercase or lowercase letters, so the field names must be underlined.

Example:

Sales: Sal Sales;

Order: Ord Order;

Detail: Dtl details;

The sales order list is named tb_Sal_Ord_Dtl;

2) If the table or field name has only one word, we recommend that you use a complete word instead of an abbreviation.

Example:

The abbreviation of the definition is Material Ma;

Item table name: tb_Material, not tb_Ma.

However, the field item code is Ma_ID, not Material_ID.

3) Add the prefix Z to the table of all stored values.

The purpose is to sort these Value List classes at the end of the database.

4) Add the prefix X to the names of all redundant classes (mainly accumulative tables ).

The redundancy class is used to improve database efficiency. It is used to add fields or tables to a non-standardized database.

5) join classes connect two basic classes with underscores and add the prefix R to name them. The names of the two tables or abbreviations of the table names are listed in alphabetical order.

An association table is used to save many-to-many relationships.

If the associated table name is greater than 10 letters, the original table name must be abbreviated. If there are no other reasons, we recommend that you use abbreviations.

For example, if a table Object has many-to-many relationships with itself, the name of the table that stores many-to-many relationships is tb_R_Object;

The table Depart and Employee have many-to-many relationships. The association table is named tb_R_Dept_Empl.

Iv. Field naming

1) Use meaningful column names. The columns in the table must adopt a complete set of design rules for keys. Each table will have an automatic ID as the primary key, and the logical primary key is defined as the first group of Candidate Primary keys. If the encoding is automatically generated by the database, it is uniformly named: ID; for custom logic encoding, use the abbreviation "ID.

For example, name Sal_Ord_ID as the number field of a sales order. If there is an automatic number generated by a database, name it: ID.

2) All database Field Names Use lowercase English words and are separated by "_". The naming rules are table alias + word, such as user_name and user_pwd. (Table alias rules. If the table name is a word, the first four letters of the word are used as the alias. If the table name is a word, take the first two letters of each word to form an alias with four letters. If the table name is composed of three words, take one of the first two words and then two letters from the last word. The result is a 4-letter alias .)

V. View naming

1) The view name consists of the prefix and actual name, and is connected with an underscore in the middle. The prefix uses the lower-case letter vi to represent the view.

Valid view names are similar to the following:

Vi_User

Vi_UserInfo

2) Naming should reflect the functions of each view as much as possible.

6. Trigger naming

The trigger uses tr as the prefix. The trigger name is suffixed with the corresponding table name. The Insert trigger is prefixed with '_ I', And the Delete trigger is prefixed with '_ d "', add '_ U' to the Update trigger, for example, tr_Customer_ I, tr_Customer_D, and tr_Customer_U.

VII. Index naming

Index name = idx + "_" + Table abbreviation + related fields/index meaning

8. Stored Procedure name

The stored procedure name consists of the prefix and actual name plus the operation name, and is connected by an underscore in the middle.

Prefix: sp, a lowercase letter, indicates the stored procedure.

Operation name: Insert | Delelte | Update | Caculate | Confirm

Valid Stored Procedure names are similar to the following:

Sp_User_Insert

9. variable name

The variable name is in lowercase. If it is in the phrase format, each word is separated by an underscore, for example, @ sys_err_no.

10. Other considerations in naming

1) The names must not exceed 30 characters. The variable name length is limited to 29 (excluding the identifier character @).

2) The names of Data Objects and variables are all English characters. Chinese names are prohibited. Do not leave spaces between characters in the object name.

3) Be careful when retaining words. Ensure that your field names do not conflict with the reserved words, database systems, or common access methods.

5) ensure the consistency of Field Names and types. Ensure consistency when naming fields and specifying data types for them. If the data type is an integer in one table, the Data Type in the other table should not be converted to the numeric type.

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.