Software design Scheme (Database design principles) __ Database

Database technology is the most effective means of information resource management. Database design is the core and foundation of establishing database and its application system, it requires to construct a better database mode for the specified application environment, set up database application system, and make the system can store the data effectively, satisfy the user's various application needs.

1, before the design of the database

1 understand customer needs, ask users how to view the future changes in demand. Let customers explain their needs, and as the development continues, often ask customers to ensure that their needs are still in the development of the purpose;

2 to understand the business, in the future development phase to save a lot of time;

3) Attach importance to input and output. When defining database tables and field requirements (input), you should first examine existing or already designed reports, queries, and views (outputs) to determine which tables and fields are necessary to support these outputs;

4 Create a data dictionary and an e-R diagram, which is absolutely necessary for the documentation of SQL expressions;

5 Define the standard object naming conventions.

2, table and field design　　

1) Table Design principles

(1) standardization and normalization;

The standardization of data helps to eliminate data redundancy in the database. There are several forms of standardization, but third normal Form (3NF) is often considered to be the best balance in performance, extensibility, and data integrity. In fact, it is sometimes necessary to standardize a table for efficiency reasons.

(2) The use of data-driven to enhance the flexibility and scalability of the system;

(3) When designing a database, consider which data fields may be changed in the future.

2) Field design principles

(1) 3 useful fields that should be added to each table;

①drecordcreationdate, under SQL Server defaults to GETDATE ();

②srecordcreator, under SQL Server defaults to not NULL default USER;

③nrecordversion, a recorded version mark, helps to accurately describe the reason for the occurrence of NULL data or loss of data in the record.

(2) The address and telephone use of multiple fields, telephone numbers and e-mail addresses preferably have their own data tables, with its own type and tag category;

(3) using the role entity definition belongs to a class of columns, creating a specific time association relationship, so that you can achieve self-documentation;

(4) Select the number type and text type to be as sufficient as possible, otherwise can not be calculated operation;

(5) Add the delete tag field. Do not delete a row individually in the relational database, but include a delete tag field in the table so that you can mark the row for deletion.

3, keys and index

1) Key Selection principle

(1) Key Design 4 principle

① All keys must be unique;

② creates a foreign key for the associated field;

③ avoid the use of composite keys;

The ④ foreign key always associates a unique key field.

(2) Using the primary key generated by the system to control the index integrity of the database, and when there is a consistent key structure, it is easy to find a logical defect;

(3) Do not use the user's key, usually do not select the user can edit the field as a key;

(4) Optional keys can sometimes be used as primary keys, can have the ability to establish a strong index.

2 Index usage principle

Indexing is one of the most efficient ways to get data from a database, and most database performance problems can be solved by indexing technology.

(1) A logical primary key uses a unique group index that uses a unique, nonclustered index for the system key (as a stored procedure) and a non group index of any foreign key columns. Consider the size of the database, how tables are accessed, and whether these accesses are primarily for reading and writing;

(2) Most databases index automatically created primary key fields, but you cannot forget to index foreign keys, which are also frequently used keys;

(3) Do not index memo/note fields, do not index large fields, so that the index takes up too much storage space;

(4) Do not index commonly used small tables, do not set any keys for small data tables, especially if they often have insert and delete operations.

4. Data integrity Design

1) Integrity Implementation Mechanism

(1) Entity integrity: Primary key

(2) Referential integrity

① Delete data in parent table: cascading delete, restricted deletion, null value;

Insert data in ② parent table: Restricted insertion, recursive insertion;

③ update data in parent table: Cascade Update, restricted update, null value.

There are two ways for DBMS to realize referential integrity: foreign key implementation mechanism (constraint rule) and trigger implementation mechanism.

(3) User-defined integrity: not Null,check, triggers.

2 Enforce data integrity with constraints rather than business rules;

3 Force indicates integrity. Remove the harmful data before entering the database, and activate the indication integrity characteristic of the database system;

4 The best way to control data integrity is to limit the user's choice by using the search control data integrity;

5 Use the view. You can create a dedicated view of your application without having to access the data table directly from your application, which is equivalent to giving you more freedom when dealing with database changes.

5, other design

1 Avoid the use of triggers , it is really best to focus on the documentation of it;

2 use English (or any other language) and do not use the code, you really need to be able to attach the code next to the user knows English;

3 Save the usual information. Having a table dedicated to general database information enables a simple mechanism to track the database, which is particularly useful for non-client/server environments;

4) contains the version mechanism, which is more convenient when modifying the database structure;

5 Prepare documents for all shortcuts, naming conventions, restrictions and functions;

6 repeated testing to ensure that the selected data types meet commercial requirements;

7 inspection design, the common technique for checking database design during development is to check the database through its supported application prototypes.

6. Database naming specification

1 The name of the Entity (table)

(1) The table is named after a noun or noun phrase, and a simple rule is defined for the alias of the table;

(2) If the table or the name of the field has only one word, then it is recommended not to use abbreviations, but with complete words;

(3) All the tables that store the values list prefix Z with the purpose of sorting the values list classes at the end of the database;

(4) The name of all redundant classes (mainly the cumulative table) preceded by prefix x. Redundant class is to improve the efficiency of the database, non-standard database when the addition of the field or table;

(5) The association class is named after the two basic classes are appended with an underscore, followed by an alphabetical list of two table names or table names. Association tables are used to save many-to-many relationships.

2 The name of the property (column)

(1) Using a meaningful column name, the column in the table to use a set of design rules for the key;

Each table will have an automatic ID as the primary, and the logical primary is defined as the first set of candidate main health. If it is a custom logical encoding, it is named with the abbreviation "ID". If the key is a numeric type, you can use _no as the suffix. If it is a character type, you can use the _code suffix. A standard prefix and suffix should be used for column names.

(2) All attributes are added to the suffix of the type, and if additional suffixes are required, they are placed before the type suffix. Data type is the field of text, type suffix TX can not write, some types of more obvious fields can also not write type suffix;

(3) using prefix naming. Using a uniform prefix for the column names of each table can be greatly simplified when writing SQL expressions, but there are drawbacks to doing so, such as damaging the automatic table connection tool.

3) The name of the view

(1) The view is prefixed with V, and other naming rules and tables are named similarly;

(2) naming should try to embody the functions of each view.

4) Naming of triggers

Triggers are prefixed with TR, and the trigger name is the corresponding table name plus the suffix, the Insert trigger plus the _i,delete trigger plus the _d, and the update trigger adds _u. such as: Tr_user_i,tr_user_d,tr_user_u.

5) Stored Procedure name

Stored procedures should start with the UP_, and the system's stored procedures to distinguish, the next part mainly in the form of movable object, and the use of the underline segmentation of the various components.

6) Variable name

Variable names are lowercase and, if they are in the form of phrases, separate each word with an underscore.

7 Other considerations in naming

(1) The above name must not exceed 30 characters of the system limit, variable name length is limited to 29 (excluding the identity character @);

(2) The names of the data objects and variables are used English characters, prohibit the use of Chinese naming, never in the object name of the characters between the spaces;

(3) Be careful to retain the word, to ensure that your field name does not and reserved words, database systems or common access methods conflict;

(4) Maintain consistency of field names and types, and ensure consistency when naming fields and specifying data types for them.