java - 面试时候问：数据库设计范式的理解

Question

如题，面试的时候，被问到：数据库设计范式的理解？只记得在大学的离散数学中学过第一范式，第二范式，这些定义，工作中还没有舍身处地的使用过这些范式，也不知道在表结构的设计当中，应当如何注意这部分的内容？

麻烦这方面的大神指点一二，非常感谢~~~

Answer 1

• First Normal Form: All field values ​​are non-decomposable atomic values. Fields such as personnel information and address do not meet the first normal form, because addresses can be subdivided into fields such as provinces and cities.

• Second normal form: Each column is related to the primary key, and cannot only be related to a certain part of the primary key (joint primary key).

• Third Normal Form: The third normal form needs to ensure that each column of data in the data table is directly related to the primary key and cannot be indirectly related.

Answer 2

In fact, there are not only three paradigms, but the school only teaches three major paradigms.
For the paradigm of understanding, I think we can start from the following aspects

The role of paradigm

• Reduces data redundancy and reduces hard drive occupancy, which improves storage efficiency (storage space was still quite expensive in the era when Paradigm came out)

• Increase data consistency.

• Abstract for easy understanding (entity-relationship)

Disadvantages of paradigm

• Increases the difficulty of query and retrieval (there are so many associations, writing SQL is almost intoxicating)

• Low query efficiency

The emergence of anti-paradigm

• Denormalize to the first normal form or the second normal form, using redundant data to speed up data query

• Although the storage efficiency is low, the current storage space cost is still relatively low.

• Increase query speed, low-value data is redundant, greatly improving query data

The emergence of paradigm-free

• The emergence of document databases (for example: mongodb) has an obvious impact on relational databases.

That’s all I’ve written, please let me know if I’m wrong.

Answer 3

The three major paradigms can be used just like definitions.
That is, when designing database tables, the most basic thing to consider is these three paradigms. The first table field is finalized, that is, when designing fields in database tables, abstract fields are generally not considered. , that is, it does not consider the fields that can be subdivided, such as: address (not detailed), information (not detailed). The address can be subdivided into country and city to specific information, and it can also include name, age, etc.
Fields are related, no redundancy, the table is independent ,Related attributes are associated to make the table into one and well mapped. The fields in the table cannot be mixed. For example, there are purchase records in the user information table and age in the order table

The last paradigm is direct correlation. The paradigm is considered to be in ascending order, and then the first two paradigms are satisfied, and then the third paradigm is considered. Whether the data in the table are directly related to each other, and whether there are direct dependencies on the fields, if there are dependencies, they are generally not considered

In some special cases, you can design a table structure that is inverse to the three normal forms. I won’t elaborate on it here, but I’ll also give you some ideas