A general understanding of the three paradigms of Database Design

 

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, the three paradigms must be understood in a general sense (the general understanding is sufficient, not the most scientific and accurate understanding ):

Paradigm 1: 1nf is an atomic constraint on attributes. Attributes must be atomic and cannot be decomposed. <br/> paradigm 2: 2nf is a uniqueness constraint on records, the record must have a unique identifier, that is, the uniqueness of the object. <br/> the third paradigm: 3nf is a constraint on field redundancy, that is, no field can be derived from other fields, it requires no redundant fields. 

No redundant database can be designed. However, databases without redundancy may not be the best. Sometimes, to improve operational efficiency, we must lower the paradigm standard and properly retain redundant data. The specific approach is to follow the third paradigm in conceptual data model design, and to reduce the paradigm standard to be considered in physical data model design. Reducing the paradigm is to add fields and allow redundancy.

The relationship between the basic table and its fields should satisfy the third paradigm as much as possible. However, database design that meets the third paradigm is often not the best design. In order to improve the efficiency of database operation, we often need to reduce the paradigm standard: appropriately increase redundancy to achieve the purpose of changing the space for time.

[Example 2]: There is a basic table for storing items, as shown in table 1. The existence of the "amount" field indicates that the table design does not meet the third paradigm, because the "amount" can be obtained by multiplying the "unit price" by "quantity, the "amount" is a redundant field. However, adding the redundant field "amount" can increase the speed of query statistics. This is the practice of changing the space for time.

In Rose 2002, two types of columns are required: Data columns and calculation columns. Columns such as "amount" are called "calculation columns", while columns such as "unit price" and "quantity" are called "Data columns ".

Duplicate primary keys and Foreign keys appear in multiple tables, not data redundancy. This concept must be clear. In fact, many people still do not know. The repeated occurrence of Non-key fields is data redundancy! It is also a low-level redundancy, that is, repetitive redundancy. Advanced redundancy does not appear repeatedly, but is derived from a field.

The "unit price, quantity, and amount" fields in a product are derived from the "unit price" multiplied by the "quantity". The "amount" field is redundant and 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 at different times, locations, and roles. Therefore, we advocate advanced redundancy (derivative redundancy) against low-level redundancy (repetitive redundancy ).