Database paradigm Design

In the software development process, database design is very important. It can be said that a good database design is a precise positioning of user needs. It not only makes software development very convenient, but also enables the software system to run efficiently, at the same time, to facilitate future maintenance or database replacement.

 

During the recent development of the system, I felt that the most rewarding thing was database operations. When I first developed the data center charging system, I had no experience and had little knowledge, so I couldn't talk about the database design at all, that is, when I felt that the database lacked some fields, modify fields directly in the database table. This adds a lot of work for you, and the relevant code needs to be modified in one place.

 

The Database Design of the fee system for the development of the. NET version data center is much better. The database design is certainly much better than the previous one because you fully understand the requirements. However, looking back, there are still many defects in the database design. There are still a large number of redundant fields in the table, and some exceptions may occur after addition, deletion, and modification.

 

In the niugu news and publishing system, because the system is very small, database operations are the same, and the complexity cannot be solved. However, I feel that the database design of instructor Niu is still very standard. Categories, comments, and news are placed in three tables, with no redundant data.

 

There is too much nonsense and we are beginning to step into the topic. Good design can help programmers get twice the result with half the effort.

 

First, let's look at the official explanation:

The first paradigm means that each column in the database table is an inseparable basic data item, and the same column cannot contain multiple values, that is, an attribute of an object cannot have multiple values or duplicate attributes.

 

The second paradigm refers to the function dependency of a database table that does not have a non-Keyword segment on any candidate keyword segment.

 

The third paradigm database table does not have a function dependency on any candidate keyword segment for non-Keyword fields.

 

To put it simply, the first paradigm refers to the atomicity of data in database tables and data cannot be divided. As long as it is a relational database, it must satisfy the first paradigm first. Let's take a look at the example. The three fields in Figure 1 cannot be further divided, so they satisfy the first paradigm. In Figure 2, the fields of major, grade, and class are placed in one field, which does not conform to the first paradigm.

Next, let's take a look at the table design in the data room charging system-the basic data table for students.

Figure

As every field in Figure A cannot be further divided, it satisfies the first paradigm. However, such a design will produce a large number of redundant fields. If a student registers two cards, the student's information needs to be repeated twice.

 

The second paradigm solves the above problems. It emphasizes that its attributes must fully depend on the primary key. The attributes of an object cannot only depend on some attributes of the primary key. If some dependencies exist, the corresponding part of this attribute and the primary key word should be separated to form a new object. The relationship between the object and the original body is one-to-multiple.

 

As in the above example, it is clear that the primary key is the combination of the card number and student number as the primary key. The name, gender, department, Major, grade, and class are only related to the student ID and only depend on the student ID, without relying on the card number, this is not in line with the second paradigm.

 

Based on the requirements of the second paradigm, we separate the student-related information. After modification, we can get two tables:

Figure B

However, it is far from enough to satisfy the database of the second paradigm. If the database meets the second paradigm, transmission dependencies may still occur. Figure B There Is A transmission relationship between the Department and major in the student table. If Xiao Hong and Xiao Ming's Department are both the Mathematics Institute and the major is the mathematics Major, then the database department will repeat twice and add redundant fields.

The third paradigm ensures that the same class of things are divided into one table, eliminating the phenomenon of passing dependencies. Based on the design requirements of the third paradigm, we separate the student table to obtain the following table:

Figure C

The following figure shows the database relationship diagram in Figure C:

Good database design is a very important step in software development. The three-paradigm design is just a standard. For the design of basic tables, we recommend that you follow the Three-paradigm requirements as much as possible. For the design of temporary tables, we can add some redundant fields as appropriate, after all, the query and retrieval speed of a single table is relatively fast, improving the system performance.