Standardization and non-standardization of Relational Database Design

Relational Database Design is a process of organizing and structuring data. The core issue is the design of relational models. A relational model is a mathematical model that uses two-dimensional table data to describe the relationships between entities. It is a collection of all relational patterns, attribute names, and keywords, and an object that describes relational patterns. The relational mode refers to the Attribute Table of a relational table, that is, the table framework of the relational table. The design of the relational model is the soul of the design of the relational model. Therefore, the design of the relational model is the core of the relational database design. The design of the relational model directly determines the performance of the relational database. At present, normalization is dominant in the design of guiding Relationship patterns. It has been developed and matured in the long-term development of databases for decades. However, in recent years, this field has seen a new trend. The design of a denormalization relational model has attracted the attention of the industry and has been applied to a certain extent. All parties have different comments on this new design idea, which leads to a small battle of standardization and non-standardization in the relevant theoretical circles. This article briefly introduces the basic idea of standardization and non-standardization design, and summarizes the main points of both sides of the debate for reference by relevant personnel in the domestic industry.

 
I. Standardized Design
The basic idea of the standardized relationship mode design is to break down the Relationship Mode and replace the original relationship mode with a set of equivalent relationship subpatterns to eliminate Data Dependencies (including function dependencies and multi-value dependencies) so that a link only describes a link between an object or an object. This process must be performed without compromising connectivity and functional dependence, that is, to ensure that the original data is not damaged, and the decomposed relationship can be restored to the original relationship through natural connections.
Specifically, the standardization design process is to break down a two-dimensional table into multiple two-dimensional tables and establish associations between tables according to different paradigms, finally, a table can describe only one object or one link between objects. The following main paradigms are followed: 1 NF, 2 NF, 3 NF, BCNF, 4 NF, and 5 NF. 3NF and BCNF are most widely used in engineering, 3 NF is recommended as the standard.
The benefits of the standardized design include effectively eliminating data redundancy, streamlining data subordination, maintaining database integrity, and enhancing database stability, scalability, and adaptability. It is generally considered that the main problem in the standardization design is to increase the join database table operation during query, resulting in the loss of computer time, space, system and operation efficiency. In most cases, this problem can be solved through good index design and other methods.

 
Ii. nonstandard design
The basic idea of nonstandard design is that the real world does not always follow a perfect mathematical relationship model. The Standardized Design of things is mandatory. The form is simplified and the content tends to be complicated. More importantly, the database operation efficiency is reduced. Normalization requires that the paradigm of the relational model be appropriately reduced or even discarded. A table is no longer required to describe only one link between an object or an object. The main purpose is to improve the database operation efficiency.
The main technologies for non-Standardization include adding redundant or derived columns, merging, splitting, or adding duplicate tables to tables. It is generally considered that normalization can be considered in the following circumstances: (1) tables involved in a large number of frequent queries must be connected; (2) during execution, major applications connect tables for query. (3) temporary tables or complex queries are required for Data calculation.
The main advantage of the nonstandard design is that it reduces the connections required for query operations, reduces the number of external keys and indexes, and can perform statistical calculations in advance, improving the response speed during query. The main problem of non-standardization is that it increases data redundancy, affects database integrity, reduces data update speeds, and increases the physical space occupied by storage tables. The most important issue is the integrity of the database. This problem can be solved by setting up a trigger, applying transaction logic, and running a batch command or stored procedure at an appropriate interval.

 
Iii. points of debate over standardization and non-Standardization
One party that supports the non-Standardization Design believes that the higher the degree of database standardization, the more tables there are, and the degree of normalization is directly related to the number of tables; the more tables, the more table join operations, the more join operations, the faster the database is executed, and the performance of the database is affected. Only through the non-standardization design can we significantly reduce the number of tables, thus reducing the dependence on connection operations and accelerating database execution speed, can we ensure the normal performance of the database. For example, the star schema that is popular in the decision support system is far better than the application standardization design. It is the best example of non-standardization design. Non-standardized design does not mean chaos or disregard of rules. It also follows the basic principles of protecting software engineering such as information integrity.
One party that supports the standardization design believes that standardization and non-standardization are only a logical concept, emphasizing that the non-Standardization designers confuse the relationship between logic and physical. The database performance is determined by the physical level, that is, the hardware, database size and physical design, data storage and access methods, the optimization degree of the database management system, and the number of concurrent accesses; the nonstandard design does not change the physical level of the database, so it is impossible to improve the database performance. Normalization is not only designed to avoid data redundancy, but also to ensure database integrity. The biggest problem with the nonstandard design is that it is difficult to ensure data consistency in the database and there is a risk of data destruction. In addition, non-standardization makes a table contain multiple entities. The combination of different entities enhances the complexity of the database, increases the difficulty of understanding the user, and leads to difficulties in describing the problem, increased the risk of correct response. Only standardized design is the fundamental way to solve these problems. If we do not abandon the design concept of non-standardization, We will ignore the risk of database integrity being damaged in order to achieve the so-called performance improvement, developers cannot be encouraged to study a truly standardized and high-performance relational database management system, and the consequences will inevitably affect the healthy development of the database.
In a sense, the standardization and non-Standardization Design of databases are not opposite. One of them may gradually die, and the two may have a middle path to go. There is a spiral process for recognizing things. The debate is not over yet, and the final results cannot be predicted. However, no matter what the results are, it will have a profound impact on the future direction of database development.

From: http://blog.csdn.net/RAINMAN_NET/archive/2005/04/13/345379.aspx