Database Table and field naming rules

Database Design tables and field naming rules (I have sorted them out. I hope you can give more suggestions) 1. database Table naming rules: (1) prefix should be added before the table name. The prefix of the table is an English abbreviation of the system or module. The prefix is capitalized or the first letter is capitalized, the first letter of a word in the table name is capitalized. (2) database table names should be meaningful, easy to understand, and the most

Database Design tables and field naming rules (I have sorted them out. I hope you can give more suggestions) 1. database Table naming rules: (1) a prefix should be added before the table name. The prefix of the table is an English abbreviation of the system or module. The prefix is capitalized or the first letter is capitalized, the first letter of a word in the table name is capitalized. (2) database table names should be meaningful, easy to understand, and the most

Database Design tables and field naming rules (I have sorted them out. I hope you can give more suggestions)

1. Database Table naming rules:

(1) A prefix should be added before the table name. The prefix of the table is abbreviated to the system or module in English. The prefix is all capitalized or the first letter is capitalized, and the first letter of the word contained in the table name is capitalized.

(2) database table names should be meaningful and easy to understand. It is best to use English words or abbreviations that can express functions. If they are expressed in English words, we recommend that you use full English words.

(3) The table name cannot be too long. It is better to have no more than 3 English words (22 letters ).

(4) The database table name should be in the singular form of English words. For example, the Employee table name should be "Employee" rather than "Employees.

(5) If it is a background table name, you should add a suffix Based on the table name.
_ B
(First Letter of back)

(6) before creating a table, add a comment to the table.

2. Table field naming rules:

(1) database table fields should be meaningful and easy to understand, preferably letters that can express the meaning of fields

(Some people think that if an English word is used as a field, because the translation tools are different, and the fields are not uniform, we recommend that you use the initials of Chinese pinyin.
Some people think that it is not intuitive to use the Chinese pinyin abbreviation, and they do not know what this field is)

(2) All inner codes in the system, that is, they are only used to identify uniqueness and identify fields used in the program. We recommend that you set the field name to ID and the type to integer or long integer.

(3) in the system, it is a serial number field in the business, representing a certain amount of business information. We recommend that you name the field
Code, such as the work order number
Wf_code.

(4) do not include data types in database table fields (column names), such as datetime

(5) do not duplicate the table name when naming the database table field (column name). You can use the first letter of the table name (excluding the database table name prefix)

Note:
Do not name database table fields (column names)
It is not recommended to use database keywords, such as name, time, datetime password, etc.

3. Table design specifications:

(1) All fields must have default values except the following data types: timestamp, image, datetime, smalldatetime, uniqueidentifier, binary, SQL _variant, binary, and varbinary. The default value of the numeric type is a null character value string '', the default value of the numeric type is 0, and the default value of the logical type is 0;

In the system, the value 0 in all logic types indicates "false", and the value 1 indicates "true ".

Datetime and smalldatetime fields have no default values and must be NULL.

(2) We recommend that you use varchar instead of nvarchar when the field is defined as a string.

Note
: In MySQL 65535 or later versions, the length of the varchar data type can be up to 65532, that is, bytes of data can be stored, and the start and expires bits occupy three
Bytes.

(3) It is recommended that the following fields be included in most tables (such as work orders:

Field Name Description type Default Value

CreatorID creator int
The default value is
0

CreatedTime creation time Datetime
The default value is NULL.

(4) Field description

A. The description (comment) must be entered for the field)

B. Try to abide by the 3NF Standard)

Each value in the table can only be expressed once (the column name is unique)

Each row in the table should be uniquely identified (ID uniqueness, such
Automatic Growth
Primary Key)

The table should not store non-key information dependent on other keys.

(5) add index rules

A. After the table is created, the database automatically generates an index for the table (
If an index is added to a column that automatically grows to generate a unique index, the database will give a warning that the column has been indexed, we recommend that you modify the index name to make it easier to use.

B. When adding an index, we recommend that the index name be consistent with the database column name for ease of use.

C. If the field is actually associated with the keywords of other tables and is not designed as a foreign key reference, you need to create an index.

D. If a field is associated with a field in another table, you must create an index.

E. If the field needs to be used for condition queries other than fuzzy queries, you need to create an index.

F. Except for the primary key words that allow the creation of a cluster index, the indexes created by other fields must be non-cluster indexes.

4. Stored Procedure naming rules

(1)
The naming rules for the stored procedure must be as follows: USP _ + system module abbreviation (similar to the table prefix) + _ + Function Identifier + represents the name of the primary table (without a prefix) for the stored procedure operation or the abbreviation of the function in English or English.

If only one table is operated in a stored procedure, we recommend that you use the name of the table operated in the stored procedure (without the prefix ). This helps you find the corresponding storage process based on the table name. For example:

Used for the new storage process USP_MESSAGE_Add_Model

Stored Procedure USP _ MESSAGE_Upt_Model used for Modification

Stored Procedure USP _ MESSAGE_Del _ Modele used for deletion

Note: USP is user stored procedure
Abbreviations

5. Stored Procedure Design Specifications

The following content must be described during storage:

(1) Purpose: To describe the role of this storage process.

(2) Author: name of the person who created the stored procedure for the first time. The full Chinese name is not allowed here.

(3) creation date: the date when the storage process is created.

(4) modification records:

The modification record must contain the sequence number, modifier, modification date, and modification reason. The modification cannot be directly modified on the original code or delete the original code, you can only comment out the original code and add the correct code again. The sequence number is changed in the form of log1, log2, and log3.... The sequence number is increased according to the order of the number of modifications, and the sequence number is changed before and after the original code block and the new code block are commented out.

(5) Chinese annotations of parameters and variables in the storage process.

Suggestion: create a text file in the database and save the creation script.

6. View naming rules

The view naming rules are as follows: UV _ + system module abbreviation (similar to the table prefix) + _ + Function Identifier + represents the name of the main table queried by the view (without the prefix) or an abbreviation of an English word or an English word.

If a view only queries one table, we recommend that you use the table name (without the prefix) of the table queried by the view ). In this way, you can find the corresponding view based on the table name.

Note: UV is short for userView.

7. View Design Specifications

The following content must be described in the View:

(1) Purpose: To describe the role of this view.

(2) Creator: name of the person who created the view for the first time. The full Chinese name is not allowed here.

(3) modifier, modification date, and reason: if someone modifies this view, the name, modification date, and reason of the modifier must be added before this view.

(4) Chinese annotations to view parameters and variables

Suggestion: create a text file in the database and save the creation script.

8. Trigger naming rules

Insert trigger plus '_ I', Delete trigger plus '_ d', Update trigger plus' _ U'

9. Trigger design specifications

The following content must be described in the View:

(1) Purpose: To describe the role of the trigger.

(2) Creator: name of the person who created the trigger for the first time. The full Chinese name is not allowed here.

(3) modifier, modification date, and reason: if someone modifies the trigger, the modifier name, modification date, and reason must be added before the trigger.

(4) Chinese annotation of each trigger parameter and variable

Suggestion: create a text file in the database and save the creation script.