Database Naming and design specifications

1. Database involves character specification

We agreed: the use of 26 letters (case-sensitive) and 0-9 of the 10 natural numbers, plus the underscore _ composition, a total of 63 characters. No other characters (except annotations) can appear.

2. Database object Naming conventions

We agreed that database objects include tables, views (queries), stored procedures (parameter queries), Functions, constraints. The name of the object consists of a prefix and a real name, not exceeding 30.

Prefix: Use lowercase letters

Table TB
View VI
Stored Procedure SP
FUNCTION fn

Actual name: The actual name as far as possible to describe the content of the entity, by the word or word combination, each word of the first letter uppercase, other letters lowercase, do not start with numbers and _. Such as

Table User_info
View User_list
Stored Procedure User_delete

Therefore, the legitimate object name is similar to the following.

Table Tbuser_info Tbmessage_detail

View Vi_message_list

Stored Procedure Sp_message_add

3. database table Naming conventions

We agreed that the table name consists of a prefix and a real name.

Prefix: Use the lowercase letter TB, which represents the table. In the actual name, a system tries to take the same word, multiple after add _ to connect the distinction.

Therefore, the legal table name is similar to the following.

Tbmember

Tbmember_info

Tbforum_board

Tbblog_comment1

Table

Table names such as Order/useraccout

Complies with the following specifications:

(1) Uniform use of singular form, against orders

(2) Capitalize the first letter, multiple words, capitalize the first letter of the word, oppose Order/useraccout/order

(3) Avoid Chinese pinyin and oppose Agentbaoci

(4) Avoid the underline connection, against User_accout (underscore for Oracle database)

(5) Avoid long names and oppose Websiteinfomationmodifyrecord

(6) Many-to-many relationship tables, ending with mapping, such as userrolemapping

(7) Avoid reserved words

4. Field Naming conventions

We agreed that the field consists of the short name of the table and the actual group of names. If this field is associated with a different field, the field name of the associated table fields is underlined.

Therefore, the valid field names are similar to the following.

Userid_meid
UserName
Userregdate

Field

Field names such as Userid/username/usertype

Complies with the following specifications:

(1) First letter lowercase, multiple words, the first letter of the word capital, against Userid/userid

(2) must have a primary key, the primary key is not directly with the ID, but the table name +id, such as Userid/orderid

(3) Commonly used field name, not directly with name, but table name +name, such as Username/ordername

(4) Commonly used in the field DESC, not directly with DESC, but the table name +desc, such as Userdesc/orderdesc

(5) must contain at least two lowercase letters before the capital letter, against Uid/oid

(6) Avoid Chinese pinyin

(7) Avoid the underlined connection

(8) Avoid too long a name

(9) Avoid reserved words

Object

(1) The stored procedure is prefixed with sp_

(2) The trigger is prefixed with TR_

(3) The function is prefixed with fn_

(4) The primary key is prefixed with pk_

(5) The index is prefixed with ix_

(6) The first letter of the prefix is capitalized, multiple words, the first letter of the word capitalization, such as Sp_countfee

(7) All letters of all keywords must be capitalized, such as select Userid,username from User

5. View Naming conventions

We agreed that the field consists of a prefix and an actual name, with an underscore connected in the middle.

Prefix: Use the lowercase letter vi to represent the view.

Therefore, the legal view name is similar to the following.

Vi_user
Vi_userinfo

6. Stored Procedure Naming conventions

We agreed that the field consists of the prefix and the actual name plus the operation name, the middle of which is connected by an underscore.

Prefix: Use the lowercase SP, which represents the stored procedure.

Operation Name: insert| delelte| update| caculate| Confirm

For example:

Sp_user_insert

7. Database Design Documentation Specifications

All database designs are written as documents, and the documents are expressed in a modular format. The approximate format is as follows:

‘-------------------------------------------

' Table name: Tbuser_info

' Yezi (leaves)

' Date: 2004-12-17

' Version: 1.0
' Description: Save user profile

' Specific content:

' UserId int, automatic incremental user code
' UserName char (12) User name

8.SQL Statement Specification

We agreed that all SQL keywords are capitalized, such as Select,update,from,order,by.