Database development norms

Jul 2 posted in Development (RSS), comments.

Database development norms

1. Field data sheets and design

Table design principles

1, standardization and regularization

Standardization of data in the database help to eliminate data redundancy. There are several forms of standardization, we are here using Third Normal Form (3NF), because the 3NF in terms of performance, scalability and data integrity to achieve the best balance.
3NF standards to comply with a table of the database includes only the basic properties of its own, when they do not have the required decomposition attributes. The relationship between the table through the foreign key is connected. It has the following characteristics: a group of specialized storage table to link the adoption of key interrelated data.

2, to consider changes

Time in the design of the database data fields, taking into account what future changes may occur.

Field design principles:
1) Each table should be useful to add the three fields, namely, the requirements of each field data sheet should not be less than 3;
2) Select the number of type and text type as far as possible sufficient. In the field for the numeric or text type, its the best than the actual length of the large number of requirements.
3) To increase the growth of the operation of automatic ID field (czid)
5) increase in the date fields to create records (CreateDate).
4) to increase the field to delete tag (isvoid):
In the table contains a "Delete Marked" field, so that line can be marked for deletion. In the relational database is not alone in the deletion of a line; general procedure used to remove the data but also to carefully safeguard the overall index.

2. . Key and index design

1, button to select the principles:

1) for the association to create a foreign key field.
2) All the keys must be unique.
3) avoid the use of composite keys.
4) is always associated foreign key field is the only button.

The use of system-generated primary key when designing the database using system-generated key as the primary key, then the actual control of the index of the integrity of the database. In this way, databases and Non-artificial effective control mechanism to store data in each line of the visit . The use of system-generated key as a primary key advantages: when the keys have the same structure, can easily find a logical flaw. Do not use the user's key (not primary key can be updated with the of) in determining what the key fields as the table, the careless users will want to edit the field. Usually do not choose the circumstances of the user can edit the fields as a key.

Optional keys can sometimes be the master key to the further use of the master key option key, you can have the ability to build a strong index.

2, the index using the principles:

Index is the data from the database to obtain one of the most efficient manner. 95% of database performance problems can be solved using indexing technology.

Most of the foreign key index is indexed automatically create the database primary key field, but do not forget the foreign key index, which is frequently used keys, such as running queries show the main table and all associated records in a table on the useful .
Do not index large-scale field (there are many characters), so the index will be taking up too much storage space.

Do not index small tables commonly used for small data tables not set any keys, if they often have to insert and delete operations do not even made. Of these insertion and deletion may be more than the maintenance of the index table space scan consume more time.

III. Data Integrity Design

Mechanisms to achieve integrity: entity integrity: primary key

Referential integrity:
Father of the table to delete data: cascade delete; restricted deleted; home space to insert the value of the parent table data: Insert restricted; recursive insert the parent table to update data: update cascade; limited update; home empty value

DBMS referential integrity of the two methods can achieve: the realization of foreign key mechanism (bound by the rules) and the trigger mechanism to achieve

The integrity of user-defined: NOT NULL; CHECK; flip-flop

Business rules rather than be bound by mandatory data integrity using the database system data integrity. This will not only be achieved through standardization of data integrity but also functional. When writing data can also trigger an increase to ensure the accuracy of the data. Do not rely on the business layer to ensure data integrity; it can not guarantee that forms between the (foreign keys) can not be imposed on the integrity of the integrity of the rules on the other.

Mandatory instructions to enter the integrity of data in the database of harmful before it removed. The instructions to activate the integrity of the database system characteristics. This data can be maintained clean and will force developers to devote more time to deal with error conditions.

Control the use of search data integrity
Control of data integrity is the best way to limit user choice. Whenever possible should be made available to the user a clear choice for the value of the list. This type of code will reduce errors and misunderstandings while providing data consistency.

In order to view the use of databases and applications with another layer of abstraction between the code, the application can view the establishment of specialized applications without having to go direct access to data sheets. It could also mean changes in dealing with the database to provide you with more freedom.

IV. Other design

1, to avoid the use of the functions of flip-flop flip-flop can usually achieve by other means. Flip-flop in the debugging process could be interference. If indeed the need for flip-flop,

2, it is necessary to concentrate on its documentation.
The use of common English (or any other language) and do not use coding.
In the creation of the drop-down menu, a list of the best statements in accordance with the sort of English. If the need for coding, coding can be attached next to the user to know English.

3, contains the version of the mechanism introduced in the database version control mechanism to determine the version of the database. Over time, the needs of the users will always be change. Ultimately may be asked to modify the database structure. The version information stored directly to the database more convenient.

4, the preparation of documents for all the shortcuts, named norms, constraints and functions should be the preparation of the document.
Used to the table, columns, triggers and other database tools plus notes. Development, support and follow-up very useful modification.

5, repeated testing to establish or amend the database, users must use the new data entered field test data. The most important thing is to allow users to test and to work together to ensure that users choose the same type of data to meet business requirements. Tests need to invest in the new database prior to the completion of the actual services.

6, to check the design in the development of the database design during the inspection of the commonly used technology is supported through the application of its prototype to check the database. In other words, for each of the final prototype of the application of expression data to ensure that you check the data model and how to remove the data view.

V. database naming convention

1, the name

Table name to the initials for the Chinese Phonetic Alphabet. That is, information to show the name of this form of Pinyin initials name, table name in the multi-purpose _ to make more easy-to-understand table. The definition of the table using table alias name the first four letters.
A list of all the stored value with the prefix of the table in front of Z. The aim is to list these values in the database to sort the last category.
All the redundant naming category (mainly accumulated form) by adding the prefix X. Redundancy is a database in order to improve efficiency, non-standardized database fields when adding category or form associated underlined through two basic types of connections, the way together with the name prefix R, listed in alphabetical order behind the two tables name or the abbreviation table.

2, column name

The use of meaningful names, table columns should be used for a set of key design rules.
Each table will have a self-ID as the primary health, primary health logical group as the first candidate to define the main health, if it is auto-generated code database, unified name: CZID; if it is a self-defined logic code is added with the initials "ID" of the method name.
If the key is the number of types, you can use as a suffix _NO; If it is the type of characters can be used _CODE suffix. On the list should be a standard prefix and suffix.
Listing all of the relevant type of suffix, if need other suffixes are placed before the type of suffix.

Note: The data type is a text field, type the suffix can not write TX. Some of the more obvious types of fields, you can not write the type of suffix.

Named using the prefix for each table column names are unified prefix, then the SQL expression in the preparation time will be greatly simplified. Do indeed have shortcomings, such as the destruction of an automatic tool to connect the role of form, the latter out of the public database link with some.

3, the view name
To view V as a prefix, other rules and table names similar to the name; name should reflect the function of the view.

4, flip-flops named
Flip-flop as a prefix to TR, the corresponding flip-flop is named with the suffix table, Insert trigger plus' _Ins', Delete trigger plus' _Del ', Update trigger plus' _Up'

5, stored procedure name
Stored procedure should be based on 'UP_' at the beginning, and systems to distinguish between stored procedures, follow-up to some of the main verb-object form of composition, and underlined the various components of the partition.
Query "_Qry", update plus "_Up".

6, variable names
Lowercase variable name used, if the phrase belongs to the form of each word separated by underscores;

7, named in the Additional Notes

1) above shall be named more than 30 characters of the system limits. Variable name length limit is 29 (excluding the logo character @).
2) data objects, variable names are phonetic characters used to prohibit the use of Chinese name. Do not name the characters in the object space between the stay.
3) to retain the word carefully, to ensure that your field names do not have to retain the word, and database systems commonly used access method or conflict 4) field names and types to maintain the consistency, in the name field and its time to specify the data type must be to ensure consistency. If the data type is an integer in a table, it may not in another table into the character.

Fourth, written specification

1, stored procedure

/------------------------------------------------- ----------------------------------

Stored procedure name: dbo.up_shop_SysLog_QrySyslog

Summary Description:
Chinese Name: Inquiry System log purposes: See the administrator in the background of all operational records database: shop_hisadmin

Call For example:

- Add operation records declare @ opercont as varchar (200)
declare @ ipaddr as varchar (20)
declare @ returnvalue as int
set @ opercont = "operating record"
EXEC @ returnvalue = [dbo]. [Up_shop_SysLog_QrySyslog] 10, @ ipaddr, @ opercont
return @ returnvalue

External link:
Superior call:
Lower call:
Enter the table:
Output table:

Original author: the original date:

Function of the amendment:
A brief description:

Revision History:

Modify the functional description: add page amendment display: Modify Date:

Remarks:

-------------------------------------------------- ---------------------------------/
CREATE PROCEDURE dbo.up_shop_SysLog_QrySyslog
@ czid int, - the operation ID
@ ipaddr varchar (20), - operation of IP
@ opercont varchar (200) - operating elements
AS
DECLARE @ returnvalue INT - Return value (10 - success; 11 - other failures)
BEGIN
SET NOCOUNT ON
<<Add a record operating statement>>
return 0
SET NOCOUNT OFF
END

2, view

/------------------------------------------------- ----------------------------------

View Name: dbo.v_shop_SysLog

Summary Description:
Chinese Name: Inquiry System log purposes: See the administrator in the background of all operational records database: shop_hisadmin

Original author: the original date:

Function of the amendment:
A brief description:

Revision History:

Modify the functional description:
Amendment: Modify Date:

Remarks:

-------------------------------------------------- ---------------------------------/
CREATE View dbo.v_shop_SysLog

AS
DECLARE @ returnvalue INT - Return value (10 - success; 11 - other failures)
BEGIN
SET NOCOUNT ON
<<Add a record operating statement>>
return 0
SET NOCOUNT OFF
END