Thoughts on data redundancy and paradigm-based Design

HypothesisThere is a "Reader table", and the table has a "borrow Certificate Status" field. This field should be set to "normal", "Report loss", or "cancel account", as shown in the following table:

Reader table (table 1 ):

Readerid	Readerstatus
1	normal
2	normal
3	report loss
4	normal
5	Cancel account

The problem is that there will be data redundancy when the record is too large. Should we separately set up a table for the "borrow Certificate Status, what is the association between the foreign key and the debit card status table in "Reader table? The following two tables:

Reader table (table 2 ):

readerid	readerstatus
1	1
2	1
3	2
4	1
5	3

Certificate of borrow status table (table 3 ):

statusid	statusname
1	normal
2	report loss
3	Cancel account

First, consider "Update exception": If you want to change the "normal" value to the "default" value in table 1, an "Update exception" will occur, resulting in data inconsistency. However, the key here is that if we are sure we will never update the three enumeration values, that is, we never need to change "normal" to "default ", then there will never be an update exception. This is also true for insertion and deletion exceptions. Therefore, when designing a database, I personally think that sometimes we don't need to follow the "three major Paradigms", but we should follow the situation (of course, most of the cases should follow ).

Paradigm is also used to adapt to changes in demand!

For more information, see the following:

Paradigm Standard
Relationship between basic tables and their fields , The third paradigm should be satisfied 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.
Examples 2 : There is a basic table for storing items, such as a table. 1 . " Amount " The existence of this field indicates that the table design does not meet the third paradigm, because " Amount " You can " Unit Price " Multiply " Quantity " Get, description " Amount " Is a redundant field. However, add " Amount " This redundant field can increase the speed of query statistics. This is the practice of changing the space for time.
In Rose 2002 The specified column has two types: Data column and calculation column. " Amount " Such a column is called " Calculation Column " , And " Unit Price " And " Quantity " Such a column is called " Data Column " .
Table 1 Table Structure of the commodity table
Product Name Product Model Unit Price Quantity Amount
TV set 29 2,500 40 100,000

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, we must understand the three paradigms ( The general understanding is sufficient, not the most scientific and accurate understanding. ) :
First paradigm: 1nf It is an atomic constraint on attributes. Attributes must be atomic and cannot be decomposed;
Second paradigm: 2nf It is the uniqueness constraint on the record and requires that the record have a unique identifier, that is, the uniqueness of the object;
Third paradigm: 3nf Is a constraint on field redundancy, that is, any field cannot be derived from other fields, it requires that the field is not redundant.
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.

Correct understanding of data redundancy
Duplicate primary key and foreign key in multiple tables , The concept of data redundancy must be clear. In fact, many people are still unclear. Duplicate non-key fields , Data redundancy! And isLow-level redundancyThat is, repetitive redundancy.Advanced RedundancyIt is not the repeated appearance of fields, but the derivation of fields.
Examples 4 & Lt;: item " Unit price, quantity, and amount " Three fields, " Amount " Is from " Unit Price " Multiply " Quantity " It is derived from redundancy and is an 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 ) .

Recommended related good article: http://www.ibm.com/developerworks/cn/db2/library/techarticles/dm-0605jiangt/