Database Design Specifications and skills Series 2

2. Database Design Skills

　　1. Before designing the database (demand analysis stage)

1) understand customer requirements, including future changes to user needs.

2) understand the business types of enterprises and save a lot of time in the development phase.

3) Pay attention to input (the data to be recorded) and output (reports, queries, and views ).

4) create a data dictionary and ER chart

A Data Dictionary (DD) is a collection of various Data descriptions. It is a description of Data in a database, that is, metadata, not the Data itself. (At least the Data Type of each field and the Primary and Foreign keys in each table should be included ).

Data item description: data item name, description, alias, data type, length, value range, value meaning, logical relationship with other data items

Data Structure Description: Data Structure name, meaning description, composition: [data item or data structure]

Data Stream Description: Data Stream name, description, data stream source, data stream direction, composition: [data structure], average traffic, peak traffic

Data storage Description: Data Storage name, description, number, inbound data stream, outbound data stream, composition: [data structure], data volume, access method

Process description: process name, description, input: [data stream], output: [data stream], processing: [brief description]

ER charts and data dictionaries allow anyone who knows the database to clearly learn how to obtain data from the database. The erdiagram is useful for displaying the relationship between tables, while the data dictionary describes the purpose of each field and any possible aliases. It is absolutely necessary to document SQL expressions.

5) define standard object naming rules

The names of various objects in the database must be standardized.
2. Table and field design (Database Logic Design)

Table Design Principles

1) standardization and standardization

Data Standardization helps eliminate data redundancy in databases. There are several forms of standardization, but Third Normal
Form (3NF) is generally considered to have the best balance in terms of performance, scalability, and data integrity. Simply put, the table design principles for databases that comply with the 3NF standard are: "One Fact
In One
Place "means that a table only contains its own basic attributes. When it is not its own attribute, it needs to be decomposed. The relationships between tables are connected by foreign keys. It has the following features: a group of tables are specially stored
Associated data connected by a key.

2) data-driven

Using data-driven, not hard-coded methods, many policy changes and maintenance are much easier, greatly enhancing system flexibility and scalability.

For example, if you want to access external data sources (files, XML
Documentation, other databases, etc.), you may wish to store the corresponding connection and path information in the table supported by the user interface. If the user interface executes tasks such as workflow (send mail, print letterhead, modify record status)
), The data that generates the workflow can also be stored in the database. Role permission management can also be completed through data-driven. In fact, if the process is data-driven, you can put a considerable amount of responsibility to use.
Users to maintain their own workflow processes.

3) consider various changes

When designing a database, consider which data fields may change in the future.

4) naming conventions for table name, report name, and query name

(Prefix naming) the naming rules between the checklist name, report name, and query name. You may soon be confused by the names of these different database elements. You can name these databases in a unified manner.
At least you should use a prefix such as Table, Query, or Report at the beginning of the Object Name. If Microsoft
Access. You can use symbols such as qry, rpt, tbl, and mod to identify objects (such as tbl_Employees ). Use sp_company
Identifies a stored procedure and uses udf _ (or similar tag) to identify a udf.

Field Design Principles:

1) three useful fields should be added to each table.

DRecordCreationDate. The default value is GETDATE () in SQL Server ()

SRecordCreator, which defaults to not null default user in SQL Server

NRecordVersion indicates the version of the record. It helps to accurately explain the causes of null data or data loss in the record.

Timeliness data should include the "Last updated date/time" field. Time tag is particularly useful for finding the cause of data problems, performing a date-based re-processing/reload of data, and clearing old data.

2) use multiple fields for the address and phone number

It is not enough to describe the street address in just one line. Address_Line1, Address_Line2, and Address_Line3 provide greater flexibility. In addition, it is recommended that you have your own data table with your phone number and email address.

3) Naming rules for the column [field] in the table (with prefix/suffix) and meaningful field names

Standard prefixes and suffixes should be used for column [field] names. For example, if the key type is a number, use the _ N suffix, character type: _ C suffix, and date type: _ D suffix. For another example, if your table contains many "money" fields, you may add a _ M suffix to each [field.
Assume there are two tables:

Customer and Order. The prefix of the Customer table is cu _. Therefore, the sub-segment names in the table are as follows: cu_name_id, cu_surname, cu_initials, and cu_address. The prefix of the Order table is or _, so the sub-segment name is:

Or_order_id, or_cust_name_id, or_quantity, and or_description.

In this way, the SQL statement for selecting all data from the database can be written as follows:

 

Select * From Customer, Order Where cu_surname = "MYNAME "; And cu_name_id = or_cust_name_id and or_quantity = 1

If these prefixes are not available, write them as follows (alias is used to differentiate them ):

 

Select * From Customer, Order Where Customer. surname = "MYNAME "; And Customer. name_id = Order. cust_name_id and Order. quantity = 1

The number of characters in the 1st SQL statements is not small. However, if the query involves five tables or more columns [fields], you will know how useful this technique is.

5) The length of the selected number type and text type should be as adequate as possible

Assume that the customer ID is 10 digits long. Then you should set the field length of the database table to 12 or 13 characters. However, this extra space can increase the database size without restructuring the entire database in the future.

6) add and delete tag Fields

The table contains a "delete tag" field to mark rows as deleted. Do not delete a single row in a relational database. It is best to clear the Data Program and carefully maintain the integrity of the index.

7) use case-insensitive object names and special characters

Use all uppercase letters and the name containing the delimiter has better readability (CUSTOMER_DATA). Do not leave spaces between characters of the Object Name.

8) Retain words with caution

Make sure that your field name does not conflict with the reserved word, database system, or common access method. For example, DESC is used as the description field name. The consequences can be imagined! DESC is the reserved word after DESCENDING. A select * Statement in the table can be used, but a lot of useless information is obtained.

9) maintain the consistency of Field Names and types.

Ensure consistency when naming a field and specifying its data type. If the field in table 1 is called "agreement_number", do not change the name in Table 2
"Ref1 ". If the data type is an integer in Table 1, do not change it to positive in table 2. Of course, when table 1 (ABC) has a key ID, it can be named
ABC_ID.

10) Avoid using triggers

The functions of a trigger can be implemented in other ways. When debugging a program, the trigger may become interference. If you do need a trigger, you 'd better document it in a centralized manner.

3. Select keys and indexes (Database Logic Design)

Reference: SQL Optimization-Index

　　4. Data Integrity Design (Database Logic Design)

1) Integrity implementation mechanism:

Entity Integrity: Primary Key

Integrity of reference:

Delete data in the parent table: cascade Delete; restricted Delete; null

Insert data in the parent table: limited insert; recursive insert

Update Data in the parent table: cascade update; restricted update; Null Value

DBMS can implement the following two methods: foreign key implementation mechanism (constraint rule) and trigger implementation mechanism. User-Defined integrity:

Not null; CHECK; trigger

2) Use constraints rather than business rules to force data integrity

The database system is used for data integrity. This includes not only integrity achieved through standardization, but also data functionality. Do not rely on the business layer to ensure data integrity; it cannot guarantee
Therefore, the integrity of the inter-(foreign key) cannot be imposed on other integrity rules. If you use constraints on the data layer, make sure that you have a way to pass the reason why the update cannot pass the constraints check in a language that the user understands.
User Interface.

3) mandatory indication integrity

Remove harmful data before it enters the database. Activate the indication integrity feature of the database system. In this way, data can be kept clean and developers can be forced to spend more time processing error conditions.

4) use search to control data integrity

The best way to control data integrity is to restrict user selection. Whenever possible, users should be provided with a clear value list for their choice. This reduces the errors and misunderstandings of the entered code and provides data consistency. Some public data is particularly suitable for searching: Country Code, Status Code, etc.

5) view

To provide another abstraction layer between the database and application code, you can create a special view for the application without requiring the application to directly access the data table. This gives you more freedom to handle database changes.

6) Distributed Data System

For a distributed system, before you decide whether to copy all the data on each site or save the data in one place, you should estimate the next five or ten years.
The annual data volume. When you transfer data to other sites, it is best to set some tags in the database field to update your tags after the target site receives your data. For this data transmission, write down
Your own batch processing or scheduler runs at specific intervals instead of letting users transmit data after daily work. Copy your maintenance data locally, such as calculating constants and interest rates, and set the version number to guarantee data
Each site is completely consistent.

7) relationship

If there is a many-to-one relationship between two entities and it may be converted to many-to-many relationships, you 'd better set them to many-to-many relationships at the beginning. Switching from the existing multi-to-one relationship to the multi-to-many relationship is much harder than the first one.

8) plan data retention and recovery

Consider data retention policies and include them in the design process to design your data recovery process in advance. The data dictionary that can be released to users/developers is used for convenient data identification and data source documentation. Write online updates to "Update queries" for future updates if data is lost.

9) react the system with Stored Procedures

Provides a set of general stored procedures to access each group to speed up and simplify the development of customer program code. A database is not only a place to store data, but also a place to simplify coding.
5. other design skills

1) Avoid using triggers

The functions of a trigger can be implemented in other ways. When debugging a program, the trigger may become interference. If you do need a trigger, you 'd better document it in a centralized manner.

2) use common English (or any other language) instead of coding

When creating drop-down menus, lists, and reports, it is best to sort them by English name. If encoding is required, you can attach the English language that the user knows to the encoding.

3) save common information

It is very useful to allow a table to store general database information. Store the current database version, recent check/repair (Access), name of the associated design document, and customer information in this table.
. In this way, a simple mechanism can be implemented to track databases. When customers complain that their databases do not meet the expected requirements and contact you, this is especially useful for non-client/server environments.

4) include version Mechanism

Introduce the version control mechanism in the database to determine the version of the database in use. After a long period of time, users' needs will always change. In the end, you may need to modify the database structure. It is more convenient to store version information directly in the database.

5) document preparation

All shortcuts, naming conventions, restrictions, and functions must be documented.

Database tools that annotate tables, columns, and triggers are used. It is useful for development, support, and tracking and modification.

Document the database, or create documents in the database itself or separately. In this way, after more than a year, I will go back to the 2nd versions, and the chances of making mistakes will be greatly reduced.

6) tests, tests, and repeated tests

After creating or revising the database, you must use the new data test data field entered by the user. The most important thing is to allow users to perform tests and ensure that the selected data type meets the commercial requirements. The test should be completed before the new database is put into the actual service.

7) Check Design

The common technology used to check the database design during development is to check the database through its supported application prototype. In other words, for each prototype application that ultimately expresses data, you must check the data model and view how to retrieve the data.