Database Design Model Diagram

Function module data design. 1. The relationship between the original document and the object can be one-to-one, one-to-many, and many-to-many. In general, they are a one-to-one relationship: an original document corresponds to only one entity. In special circumstances, they may be one-to-many or many-to-one relationships, that is, an original document corresponds to multiple entities, or multiple original

Function module data design. 1. The relationship between the original document and the object can be one-to-one, one-to-many, and many-to-many. In general, they are a one-to-one relationship: an original document pair corresponds to only one entity. In special circumstances, they may be one-to-many or many-to-one relationships, that is, an original document corresponds to multiple entities, or multiple original

Function module data design.

1. Relationship between original documents and entities
It can be a one-to-one, one-to-many, and many-to-many relationship. In general, they are a one-to-one relationship: an original document pair

Corresponds to only one entity. In special circumstances, they may be one-to-many or many-to-one relationships, that is, an original document corresponds to multiple instances.

, Or multiple original documents correspond to one entity. The entity here can be understood as a basic table. After clarifying the corresponding relationship, we design

The input interface is advantageous.
[Example 1]: A copy of employee resume. In the human resources information system, there are three basic tables: employee basic information table and society.

Relationship table and work history table. This is

　　

You can also disable primary keys.
The Design of primary keys and Foreign keys plays an important role in the design of global databases. After the design of the global database is complete, the VM has

American database design experts said:

It also reflects his core information system

Foreign key pair, indicating the connection between entities.

　　
　　
(4) stability. The structure of the basic table is relatively stable, and the records in the table must be stored for a long time.
After understanding the nature of the basic table, you can distinguish the basic table from the intermediate table and temporary table when designing the database.

　　

The best design. In order to improve the operational efficiency of the database, it is often necessary to reduce the paradigm standard: appropriately add redundancy to achieve a space-for-time

.
Examples

The third paradigm, American space, because

"
In

Unit Price
Table
　　
5. A general understanding of the three paradigms
A general understanding of the three paradigms is of great benefit to database design. In database design, in order to better apply the three paradigms

Three paradigms must be understood in plain words
First paradigm:
Third paradigm: 3NF is a constraint on field redundancy, that is, any field cannot be derived from other fields, and it requires that the field is not redundant.

.
No redundant database can be designed. However, non-redundant databases may not be the best database.

For row efficiency, we must lower the paradigm standard and retain redundant data as appropriate. The specific approach is to follow the third paradigm in conceptual data model design.

To lower the paradigm standard to be considered in the design of the physical data model. Reducing the paradigm is to add fields and allow redundancy.

6. Be good at identifying and correctly handling many-to-many relationships
If there is a many-to-many relationship between two entities, this relationship should be eliminated. The solution is to add a third instance between the two instances.

Body. In this way, the original multi-to-many relationship is now two one-to-many relationships. We need to rationally allocate the attributes of the original two Entities

To three entities. The third entity here is essentially a complex relationship, which corresponds to a basic table. Generally speaking, the number

Data warehouse design tools cannot identify many-to-many relationships, but can process many-to-many relationships.
Examples

The relationship is a typical multi-to-many relationship: A book can be borrowed by multiple readers at different times, and a single reader can borrow multiple

This book. To this end, you need to add a third entity between the two, which is named

Also sign

It can work

　　
　　

It is a combination of physical field names or field names. However, the former is better than the latter. When PK is a combination of field names, it is recommended that the fields

The number should not be too large. If the number is too large, the index takes up a large amount of space and the speed is slow.

　　

. The repeated occurrence of Non-key fields is data redundancy! It is also a low-level redundancy, that is, repetitive redundancy. Advanced redundancy is not a field

Repeated appears, but the field is derived.
Examples

It is redundant, and it is a kind of advanced redundancy. Redundancy is designed to speed up processing. Only low-level redundancy increases

Data inconsistency, because the same data may be input multiple times from different time points, locations, and roles. Therefore, we advocate advanced Redundancy

Endogenous Redundancy

　　

Range and function content are feasible. Instead, modify the E--R diagram. Although it does not have the only standard answer, it does not mean it can be random.

Design. Okay.

10. View technology is useful in Database Design
Unlike basic tables, code tables, and intermediate tables, a view is a virtual table that depends on the real table of the data source. View is for programmers

Using a window of a database is a form of comprehensive basic table data, a method of data processing, and a type of user data confidentiality.

Means. In order to perform complex processing, increase computing speed, and save storage space, the definition depth of a view generally cannot exceed three layers. If Layer 3

Insufficient View

Not limited.
Views play a more important role in certain information systems related to national political, economic, technical, military, and security interests. These

After the physical design of the basic table of the system is completed, the first layer view is created on the basic table. The number and structure of this layer view are as follows:

The number and structure are exactly the same. It is also stipulated that all programmers are only allowed to operate on The View. Only the database administrator,

With the knowledge of multiple personnel

11. Intermediate tables, reports, and temporary tables
An intermediate table is a table that stores statistical data. It is designed for data warehouses, output reports, or query results. Sometimes it does not have a primary key

Foreign key

Protection: temporary tables are automatically maintained by programmers themselves.

　　
Integrity of reference: Use
User-Defined Integrity: it is a business rule that is implemented using stored procedures and triggers.

　　
　　

Repeated redundant entities form a high degree of abstraction for the objective world, and carry out systematic data integration to prevent patching design;
(2) The fewer fields that combine primary keys in a table, the better. Because of the primary key function, one is to create a primary key index, and the other is as a sub-table.

The foreign key and website space. Therefore, the number of fields in the combination of primary keys is less, which not only saves the running time, but also saves the index storage space;
(3) The fewer fields in a table, the better. Only when the number of fields is small can it indicate that there is no duplicate data in the system, and

There is little data redundancy, and more importantly, it urges readers to learn

, Leaving many blank fields in the master table. The so-called

Sub-table. This method is very simple. Some people just don't get used to it, don't adopt it, don't execute it.
The practical principle of database design is to find a proper balance between data redundancy and processing speed.

A single principle cannot be isolated. This principle is relative, not absolute.

Thought: if the system has the same function, one hundred entities will be overwritten.

Of

The file system is integrated into the application database, the application database is integrated into the topic database, and the topic database is integrated into the global integrated database.

The higher the degree of integration, the stronger the data sharing, the fewer information islands, and the global information system of the entire enterprise

Number, number of primary keys, and number of attributes.
Advocate

The database is changed to a randomly designed database table.

The data tables, intermediate tables, and temporary tables are disorganized and countless. As a result, the information systems of enterprises and institutions cannot be maintained and paralyzed.
　　

It requires a high level of database design skills and art. Not everyone can do it, because this principle is to put an end to it.

Use

　　
　　

File System

Take a value of the primary key PK of the table as the boundary, and horizontally split the records of the table into two tables. If a table contains too many fields, for example

Divide the table vertically and divide the original table into two tables.
　　
　　
In short, to improve the operational efficiency of the database, the database system-level optimization, database design-level optimization, and program-level optimization must be performed.

At the same time.

The fourteen skills mentioned above are gradually summarized by many people in a large number of database analysis and design practices. For those experienced

Readers cannot stick to it, but must understand it, be realistic, and be flexible. And gradually achieve: release in the Application

Development and Application in development.