What is the database paradigm

Preface ：

About the database paradigm , I have often heard of , I haven't been able to understand in detail . A general database book or database course will introduce content related to the paradigm , Paradigms often appear in database exam questions . I don't know if you have a clear understanding of paradigms ？ In this article, let's learn the database paradigm .

1. Introduction to database paradigm

In order to establish less redundancy 、 A well structured database , There are certain rules that must be followed when designing a database . In a relational database, such rules are called paradigms . A paradigm is a summary of a design requirement . To design a relational database with reasonable structure , Must meet certain paradigms .

The English name of the paradigm is Normal Form , abbreviation NF . It's English E.F.Codd In the last century 70 After the introduction of relational database model in the s . Paradigm is the basis of relational database theory , It is also the rules and guiding methods that we should follow in the process of designing database structure .

At present, there are six common paradigms of relational database ： First normal form （1NF）、 Second normal form （2NF）、 Third normal form （3NF）、 buss - The COD paradigm （BCNF）、 Fourth normal form (4NF） And the fifth paradigm （5NF, Also called perfect paradigm ）. The paradigm that meets the minimum requirements is the first paradigm （1NF）. On the basis of the first paradigm, it is called the second paradigm （2NF）, The other paradigms are analogies .

2. Common paradigms are explained in detail

When designing a database , Will refer to the paradigm requirements to do , But that's not to say that the higher the paradigm level, the better , The paradigm is too high, although it has better constraints on data relations , But it can also lead to more complicated relationships between tables , This results in more tables per operation , Database performance degradation . Usually , In relational database design , The highest is to follow BCNF , Generally speaking, it is still 3NF . That is, in general , We have enough of the first three paradigms . Let's take a closer look at the first three common paradigms .

First normal form （1NF）

The first paradigm is the most basic paradigm . If all field values in the database table are atomic values that cannot be decomposed , It shows that the database table satisfies the first paradigm . In short, the first paradigm is that the data in each row are indivisible , Cannot have more than one value in the same column , If there are duplicate attributes, you need to define a new entity .

Example ： Suppose a company wants to store the names and contact information of its employees . It creates a table like this ：

Two employees （Jon＆Lester） Have two cell phone numbers , So the company stores them in the same form , As shown in the table above . Then the table doesn't match 1NF , Because the rules say “ Each attribute of a table must have atoms （ Single ） value ”,Jon＆Lester Staff emp_mobile Value violates the rule . In order to make the table conform to 1NF , We should have the following table data ：

Second normal form （2NF）

The second paradigm goes one step further than the first . The second paradigm needs to ensure that every column in the database table is related to the primary key , You can't just relate to a part of the primary key （ Mainly for the union primary key ）. That is to say, in a database table , Only one kind of data can be saved in a table , It is not allowed to save multiple data in the same database table .

+----------+-------------+-------+
| employee | department  | head  |
+----------+-------------+-------+
| Jones    | Accountint  | Jones |
| Smith    | Engineering | Smith |
| Brown    | Accounting  | Jones |
| Green    | Engineering | Smith |
+----------+-------------+-------+

The table above describes the employed , The relationship between the work department and the leader . We use data that uniquely represents a row of a table in the database as the primary key of the table . In the table head Columns are not related to primary keys . therefore , The table does not conform to the second normal form , To make the table above conform to the second normal form , You need to split it into two tables ：

-- employee  Primary key 
+----------+-------------+
| employee | department  |
+----------+-------------+
| Brown    | Accounting  |
| Green    | Engineering |
| Jones    | Accounting  |
| Smith    | Engineering |
+----------+-------------+

-- department  Primary key 
+-------------+-------+
| department  | head  |
+-------------+-------+
| Accounting  | Jones |
| Engineering | Smith |
+-------------+-------+

Third normal form （3NF）

Satisfy 2NF Under the premise of , All fields other than the primary key must be independent of each other , That is, you need to make sure that every column in the data table is directly related to the primary key , Not indirectly .

In short , Third normal form （3NF） It is required that a relationship does not contain non primary key information that has been included in other relationships . for example , There is a department information table , Each of these departments has a department number （dept_id）、 Department name 、 Department profile and other information . Then after the department number is listed in the employee information table, the Department name can no longer be 、 Department profile and other information related to the Department will be added to the employee information form . If there is no department information table , According to the third paradigm （3NF） It should also be built , Otherwise, there will be a lot of data redundancy .

3. About the anti paradigm

The advantages of paradigms are obvious , It avoids a lot of data redundancy , Save storage space , Keep the data consistent . The normalized table is usually smaller , Can be better placed in memory , So it's faster . So is it necessary to normalize all tables as 3NF after , Database design is the best ？ It doesn't have to be . The higher the paradigm, the finer the table partition , The more tables you need in a database , The user has to spread the data that was originally associated to multiple tables . A slightly more complex query statement may require at least one Association on a normal database , Maybe more , It's not only expensive , It may also invalidate some indexing strategies .

So when we design a database , It doesn't exactly follow the paradigm , Sometimes there's anti paradigm design . Improve database read performance by adding redundant or duplicate data , Reduce the number of associated queries ,join Times of table .

Reference resources ：

• https://segmentfault.com/a/1190000037544475
• https://www.zhihu.com/question/24696366
• https://en.wikipedia.org/wiki/Database_normalization