Some design and naming conventions in database development

Some design and naming conventions in database development 2011-03-21 13:41 Great white sharks. Blogfont Size:T | T

Database development involves many aspects of the content, this article will introduce a part of you, I hope you can harvest what you need.

Ad:51cto Net + the first App innovation contest hot Start-----------super million resources for you to take!

Before we have introduced a part of the database development process in the specification, that is, the database Development specification Detailed introduction (i), the following will continue to introduce more about database development of the specification requirements.

I. Other designs

1. Avoid using triggers
The functionality of a trigger can often be implemented in other ways. Triggers can become interference when the program is debugged. If you do need to use a trigger,

2. To centralize the documentation of it.
Use common English (or any other language) instead of coding.
It is best to sort by English name when creating drop-down menus, lists, and reports. If you need to encode, you can attach the user-known English to the code.

3. Include version mechanism
Introduce version control mechanisms in the database to determine the version of the database in use. For a long time, the user's needs will always change. The database structure may eventually be required to be modified. It is more convenient to store the version information directly in the database.

4. Document Preparation
All shortcuts, naming conventions, restrictions, and functions are documented.
Use database Tools that annotate tables, columns, triggers, and so on. is useful for developing, supporting, and tracking changes.

5. Repeated testing
After the database is established or revised, the data fields must be tested with the user's newly entered data. Most importantly, let the user test and work with the user to ensure that the chosen data type meets the business requirements. Testing needs to be done before the new database is put into actual service.

6. Inspection Design
A common technique for checking database design during development is to check the database through its supported application prototypes. In other words, the prototype application for each of the final expression data ensures that you examine the data model and see how the data is fetched.

Second, the database naming specification

1, the name of the table

The table is named after the Chinese pinyin abbreviation. That is, it is named after the name of the table information, and the table name is used more _ to make the table names more understandable. The table alias definition takes the first four letters of the table name.
All the tables that store the value list are preceded by the prefix Z. The goal is to sort these value list classes at the end of the database.
The naming of all redundant classes (mostly cumulative) is preceded by the prefix x. Redundancy class is to improve the database efficiency, the non-normalized database when the field or table association class by using an underscore after connecting two basic classes, and then prefixed by the name of R, followed by alphabetical list of two table names or table name abbreviations.

2, the name of the column

With meaningful column names, the columns in the table have a set of design rules for the key.
Each table will have an automatic ID as the primary key, the logical master as the first set of candidate key to define, if the database is automatically generated by the code, the unified name is: Czid; If a custom logical encoding is abbreviated with an "ID" method named.
If the key is a numeric type, you can use _no as the suffix, or the _code suffix if it is a character type. A standard prefix and suffix should be used for column names.
All column names are appended to the suffix of the type, and if additional suffixes are required, precede the type suffix.

Note: The data type is a field of text, and type suffix TX can not be written. There are some types of fields that are more obvious, and you can not write type suffixes.

Named with prefix
Using a uniform prefix for each table's column names can be greatly simplified when writing SQL expressions. There are drawbacks to this, such as the role of the Automatic Table Connection tool, which links public listing names to certain databases.

3, the name of the view
Views are prefixed with V, and other naming conventions are named similar to tables, and naming should reflect the capabilities of each view as much as possible.

4, the name of the trigger
The trigger is prefixed with TR, the trigger name is the corresponding table name plus suffix, insert trigger plus ' _ins ', delete trigger Plus ' _del ', UPDATE trigger plus ' _UP '

5. Stored Procedure Name
The stored procedure should begin with ' Up_ ', and the system's stored procedures are differentiated, the following sections are mainly made of the form of the verb-bin, and the components are divided by underscores.
Query Add "_qry", update Add "_up".

6. Variable Name
The variable name is lowercase, if it belongs to the phrase form, each word is separated by an underscore;

7. Other matters needing attention in naming

1) The above name must not exceed the system limit of 30 characters. The variable name has a length limit of 29 (excluding the identity character @).
2) The names of data objects and variables are phonetic characters, and Chinese names are forbidden. Never leave spaces between the characters of the object name.
3) Be careful to keep the word, and make sure that your field name does not conflict with reserved words, database systems, or common access methods
4) Maintain the consistency of field names and types, and ensure consistency when naming fields and specifying data types for them. If the data type is an integer in one table, it will not become a character type in the other table.

These specifications are in the database development process must be strictly adhered to, I hope you in the future of the database development work, must have mastered these specifications, this article will be your preferred reference article.

Some design and naming conventions in database development