[Excerpt] Database Design Process

Database Design(Database Design) Refers to the optimal construction of a given application environment.DatabaseModeTo create databases and their application systems so that they can effectively store data and meet the needs of various user applications.Requirement(Information Requirements and handling requirements ).

In the database fieldClassThe system is collectively referred to as a database application system.

I. databases and information systems
(1) databases are the core and basis of information systems. They organize a large amount of data in information systems according to certain models to provide storage, maintenance, and data retrieval.
Function, so that the information system can obtain the required information from the database in a convenient, timely and accurate manner.
(2) database is the key to whether all parts of the information system can be closely integrated and how to integrate them.
(3) database design is an important part of the development and construction of information systems.
(4) technologies and knowledge that database designers should possess:
Basic knowledge of databases and Database Design Technology
Basic knowledge of computer science and programming methods and skills
Principles and Methods of Software Engineering
Application knowledge

Ii. Database Design Features
Database construction is a combination of hardware, software, and dry parts.
Three-point technology, seven-point management, and 12-point basic data
The interface for technology and management is called "dry parts"
Database design should be combined with Application System Design
Structure (data) Design: design the database framework or structure
Behavior (processing) Design: design applications and transactions
Design of Structure and behavior Separation
Traditional Software Engineering ignores the analysis and abstraction of Data semantics in applications. as long as possible, it is possible to postpone the decision-making of Data Structure Design. Early database design focuses on the research of data models and modeling methods, ignoring the design of behavior

III. Brief description of Database Design Methods
manual trial collection method
the design quality is directly related to the experience and level of designers.
lack of scientific theories and support for engineering methods, engineering Quality is hard to guarantee
after the database runs for a period of time, various problems are often found to varying degrees, added the maintenance cost
standard design method
manual design
basic idea
process iteration and gradual refinement
standard design method (continued)
typical method:
(1) New Orleans method: database design is divided into four stages
S. b. yao method: database design is divided into five steps
I. r. palmer method: Take database design as a step-by-step process
(2) Computer Aided Design
oracle designer 2000
Sybase powerdesigner

Iv. basic steps of Database Design
Database Design Process (six phases)
1. Demand analysis stage
Accurately understand and analyze user requirements (including data and processing)
Is the foundation of the entire design process and the most difficult and time-consuming step
2. Conceptual Structure Design Stage
It is the key to the whole database design.
A conceptual model independent of a specific DBMS is formed by integrating, summarizing, and abstracting user requirements.
3. Logical Structure Design Stage
Converts a conceptual structure to a data model supported by a DBMS.
Optimize it
4. Physical database design phase
Select a physical structure (including storage structure and access method) that best suits the application environment for the Logical Data Model)
5. database implementation stage
Use the Data Language, tool, and host language provided by DBMS, and follow the results of logical and physical design.
Create databases, compile and debug applicationsProgramOrganize data warehouse receiving and perform trial run
6. Database Operation and Maintenance
The database application system can be put into operation after trial run.
The database system must be constantly evaluated, adjusted, and modified during operation.
Design Features:
In the design process, the design of the database and the design of data processing in the database are closely integrated to analyze, abstract, design, and implement these two requirements at the same time, complement each other to improve two aspects of design

Design description for each stage of the design process:

V. Formation of database models at all levels
1. Demand analysis stage: comprehensive application requirements of each user
2. Conceptual design phase: the formation of a concept model independent of the machine characteristics, independent of various DBMS products (E-R diagram)
3. logic Design Stage: first, the E-R diagram is converted into a specific database product support data model, such as the relational model, form the database logic model; then according to the requirements of user processing, security considerations, create a view based on the basic table to form an external data mode.
4. Physical Design Phase: arranges physical storage based on DBMS characteristics and processing needs, establishes indexes, and forms the internal database mode.

6. Database Design Skills

1. Before designing the database (demand analysis stage)
1) understand customer requirements and ask users how to view future demand changes. Ask the customer to explain their needs, and as the development continues, ask the customer frequently to ensure that their needs are still under development.
2) understanding enterprise business can save a lot of time in the future development phase.
3) Pay attention to input and output.
When defining database tables and field requirements (input), Check Existing or designed reports, queries, and views (output) to determine which tables and fields are necessary to support these outputs.
For example, if you want a report to be sorted, segmented, and summed by zip code, make sure that it contains a separate zip code field instead of mixing the zip code into the address field.
4) create a data dictionary and ER chart
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 achieved the best balance in terms of performance, scalability and data integrity. To put it simply, the table design principle for databases that comply with the 3nf standard is: "One fact in one place", that is, a table only includes its basic attributes, when they are not their own properties, they need to be decomposed. The relationships between tables are connected by foreign keys. It has the following features: a group of tables specifically store the associated data connected by keys.
For example, a 3nf database storing customers and their related orders may have two tables: customer and order. The Order table does not contain any information about the customer associated with the order, but stores a key value. The key points to the row in the customer table that contains the customer information.
In fact, for the sake of efficiency, it is sometimes necessary not to standardize the table.
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 documents, and other databases) on the user interface, you may wish to store the connection and path information in the user interface support table. In addition, if you execute tasks such as workflows on the user interface (such as sending emails, printing letterhead, and modifying record status), the workflow data 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 push a considerable amount of responsibility to users to maintain their own workflow processes.
3) consider various changes
When designing a database, consider which data fields may change in the future.
For example, the last name is the same as the last name of a Western person, for example, the last name of a female after marriage ). Therefore, when a system is created to store customer information, the last name field is stored in a separate data table, and fields such as the start date and end date are appended, in this way, we can track the changes of this data entry.

Field Design Principles
4) three useful fields should be added to each table
Drecordcreationdate. In VB, the default value is now (), while in SQL Server • The default value is getdate ()
Srecordcreator, which defaults to not null default • User in SQL Server
Nrecordversion indicates the version of the record. It helps to accurately describe the cause of NULL data or data loss in the record •
5) 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. Also, it is best to have your own data table with your phone number and email address. Type And tag category.
6) use the role entity to define columns of a certain type
When defining things belonging to a specific category or with a specific role, you can use the role entity to create a specific time-related relationship, so as to achieve self-documenting.
Example: Use the person entity and person _ Type Entity to describe people. For example, when John Smith and engineer are promoted to John Smith, Director and finally to John Smith, the CIO is high, all you need to do is change the key value of the relationship between person and person_type in two tables, and add a date/time field to know when the change will happen. In this way, your person_type table contains the possible types of all persons, such as associate, engineer, ctor, CIO, or CEO. Another alternative is to change the person record to reflect the change of the new title, but in this way, the specific time of the individual's position cannot be tracked in time.
7) Select numeric and text types as adequate as possible
Be careful when using smallint and tinyint types in SQL. For example, if you want to see the total monthly sales amount and the Field Type of the total amount is smallint, you cannot perform the calculation if the total amount exceeds $32,767.
Text fields of the ID type, such as the customer ID or order number, should be set to be larger than expected. 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 does not need to be occupied in the future. Reconstruction The entire database can increase the database size.
8) 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.

3. key selection and indexing (Database Logic Design)
key selection principles:
1) Key design 4 Principles
creates a foreign key for the associated field. •
all keys must be unique. •
avoid using compound keys. •
Foreign keys are always associated with unique key fields. •
2) use the primary key generated by the system
when designing a database, use the key generated by the system as the primary key, the index integrity of the database is actually controlled. In this way, the database and non-manual mechanism effectively control access to each row of stored data. Using the system-generated key as the primary key has another advantage: when having a consistent key structure, it is easy to find logical defects.
3) do not use the user's key (do not make the primary key updatable)
when determining the fields used as the table key, you must be careful with the fields to be edited. Generally, do not select an Editable field as the key.
4) optional keys can sometimes be used as primary keys
use the optional keys as primary keys, you can build powerful indexes.

indexing principles:
indexing is one of the most efficient ways to obtain data from a database. Index technology can be used to solve 95% of database performance problems.
1) logical primary keys use a unique group index to pair system Keys (as stored procedures) unique non-group indexes are used, and non-group indexes are used for any foreign key columns. Consider the size of the database space, how tables are accessed, and whether these accesses are mainly used for reading and writing.
2) most databases Index automatically created primary key fields, but do not forget the index foreign key, they are also frequently used keys, such as running a query to display a record of the master table and all associated tables.
3) do not index Memo/Note fields, or index large fields (with many characters ), in this way, the index occupies too much storage space.
4) do not index frequently used small tables
do not set any keys for small data tables, if they often have insert and delete operations, let alone this. The index maintenance for these insert and delete operations may consume more time than the scan tablespace.

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 provides two methods to achieve the integrity of the Reference: The foreign key implementation mechanism (constraint rules) and the 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. When writing data, you can also add triggers to ensure data correctness. Do not rely on the business layer to ensure data integrity; it cannot guarantee the integrity between tables (Foreign keys), so it cannot be imposed on other integrity rules.
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 will reduce the number of inputCodeErrors and misunderstandings are also provided for 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.

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. This is especially useful for non-client/server environments when customers complain that their databases do not meet the expected requirements.
4) include version Mechanism
Introduce in Database Version Control 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.