Shulou(Shulou.com)06/02 Report--

This article mainly explains "detailed explanation of Mysql paradigm and anti-paradigm". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "the detailed explanation of Mysql paradigm and anti-paradigm".

The first normal form (1NF)

The first paradigm emphasizes the atomicity constraint of attributes, which requires that attributes are atomic and can not be decomposed again.

For example: activity table (activity code, activity name, activity address), assuming that in this scenario, the activity address can be subdivided into country, province, city, urban area, location, then it does not reach the first paradigm.

Second normal form (2NF)

The second paradigm emphasizes the uniqueness constraint of the record, the table must have a primary key, and the columns that are not contained in the primary key must be completely dependent on the primary key, not just a portion of the primary key.

For example: version table (version code, version name, product code, product name), where the primary key is (version code, product code). In this scenario, the database design does not conform to the second paradigm, because the product name only depends on the product code. There is partial dependence. Therefore, in order to satisfy the second normal form, it can be transformed into two tables: version table (version code, product code) and product table (product code, product name).

The third normal form (3NF)

The third paradigm emphasizes the constraint of attribute redundancy, that is, non-primary key columns must be directly dependent on the primary key.

For example, an order table (order code, customer code, customer name), in which the primary key is (order code). In this scenario, the customer code and customer name are completely dependent on the primary key, so they conform to the second paradigm, but the customer name depends on the customer code, which indirectly depends on the primary key, so it can not satisfy the third paradigm. In order to meet the third normal form, we can split two tables: the order table (order code, customer code) and the customer table (customer code, customer name). The split database design can fully meet the requirements of the third paradigm.

It is worth noting that the focus of the second paradigm is whether the non-primary key column is completely dependent on the primary key or on part of the primary key. The focus of the third paradigm is whether non-primary key columns are directly dependent on primary keys or non-primary key columns.

Anti-paradigm

Paradigm can avoid data redundancy, reduce the space of database, and reduce the trouble of maintaining data integrity. However, through the database paradigm design, there will be more tables involved in the database business, and it may be necessary to query the business tables involved in multi-table join, which will lead to poor performance and is not conducive to sub-database and sub-tables. Therefore, for the consideration of performance priority, it may be necessary to use the design of anti-normal form in the structure of the database, that is, space for time, and adopt the way of data redundancy to avoid associated queries between tables. As for the problem of data consistency, because it is difficult to meet the strong consistency of data, in general, make the stored data as consistent as possible to ensure that the system achieves consistency after a short period of self-recovery and correction.

You need to be cautious about using anti-patterns to design databases. In general, use stylized database design as much as possible, because stylized database design makes the product more flexible and maintains data integrity at the database layer.

Sometimes, the best way to improve performance is to keep redundant data in the same table, and if you can allow a small amount of dirty data, it is a very good way to create a completely separate summary or cache table. For example, designing a "download times table" to cache the download times information can improve the speed of querying the total number of information in the case of massive data.

In another typical scenario, columns of BLOB and TEXT types may be used to store JSON structure data for extensibility reasons. The advantage is that new properties can be added to this field at any time without the need to change the table structure. However, the disadvantage of this design is also obvious, that is, the whole field content needs to be decoded to obtain the specified properties, and can not be indexed, sorted, aggregated and other operations. Therefore, if you need to consider more complex usage scenarios, it is more recommended to use a document database such as MongoDB.

It is difficult to model a real scene, and performance is generally chosen to ensure ultimate consistency.

At this point, I believe you have a deeper understanding of the "detailed description of Mysql paradigm and anti-paradigm". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.