The dispute between normalization and non-normalization of relational database design

relational database design is the process of organizing and structuring data, and the core problem is the design of relational model. A relational model is a mathematical model that describes the relationship between entities by using two-dimensional tabular data, which is the collection of all relational schemas, attribute names, and keywords, and is the object of relational schema description. A relational pattern is a list of property names for a relationship, that is, a table frame of a two-dimensional table. The design of relational pattern is the soul of relational model design. Therefore, the design of relational pattern is the core of the core of relational database design.

The design of the relational schema directly determines the performance of the relational database. At present, the standardized (normalization) design is dominant in the design of the instructional relational model, which is produced and matured in the long-term development of the database for several decades. But in recent years there has been a new trend in this field, a kind of relational model called non-standard has aroused the concern of the industry and has been applied to a certain extent. To this new design thought, each side reaction is different and mixed, thus in the relevant theorists set off a modest normalization and non-standard dispute. This paper briefly introduces the basic idea of the standardization and non-standard design, and summarizes the main points of the pros and cons, for the reference of domestic industry related personnel.

First, the standardized design

The basic idea of the canonical design of relational pattern is to decompose the relational pattern instead of the original relational schema, a set of equivalent relational models is used to eliminate the unreasonable parts of data dependency (including function dependency and multivalued dependency), so that a relationship can only describe a relation between an entity or an entity. This process must be done without compromising connectivity and maintaining functional dependencies, ensuring that the existing data is not corrupted and can be

The relationship between the solutions is restored to the original relationship through a natural join.

Specifically, the process of standardized design is based on different paradigms, a two-dimensional table is continuously decomposed into a number of two-dimensional tables and establish the association between tables, and finally reached a table to describe only a single entity or entity of a joint

The goal of the department. The main paradigms currently followed include 1 NF, 2 NF, 3 NF, BCNF, 4NF, and

5NF and so on, 3NF and BCNF are the most widely used in engineering, and it is recommended to use 3 NF as standard.

The advantages of standardized design include effectively eliminating data redundancy, straightening out data dependencies, maintaining database integrity, enhancing database stability, scalability and adaptability. It is generally believed that the canonical design exists in the master

The problem is to increase the query of the connection table operation, resulting in computer time, space, system and operating efficiency loss. In most cases, this problem can be solved by means of good index design.

Ii. Non-standardized design

The basic idea of non-standard design is that the real world does not always comply with a perfect mathematical relationship model. It is imperative to standardize the design of things, the form appears to be simplified, the content tends to be complicated, more importantly, the efficiency of database operation is reduced. Non-normalization requires a proper reduction or even abandonment of relational patterns, and no longer requires a table to describe only one relationship between an entity or an entity. Its main purpose is to

Improve the efficiency of database operation.

The main techniques for nonstandard processing include adding redundant or derived columns, merging, splitting, or adding repeating tables to a table. It is generally considered that nonstandard processing can be considered in the following situations: (1) A large number of frequent queries

The tables involved in the process need to be connected, (2) The primary application joins the table for querying when it executes, (3) The calculation of the data requires a temporary table or a complex query.

The main advantage of the non-standard design is that it reduces the connection required by the query operation, reduces the number of external keys and indexes, and can advance statistical calculation to improve the response speed of the query. The main problems of non-normalization

It increases the data redundancy, affects the integrity of the database, reduces the speed of data update, and increases the physical space occupied by the storage table. The most important of these is database integrity issues. This issue can generally be built by

Such methods as trigger, application transaction logic, running batch commands or stored procedures at appropriate intervals are resolved.