I am the way to improve the architecture, click on the top of the “attention”, adhere to share technical dry goods for you every day, private letter I reply to “01”, send you a programmer growth advanced gift package.

preface

In the daily business development process, we often need to deal with database tables. The design paradigm is the basic principle of data table design, and it is easy to ignore the existence of data table design paradigm. Many times, after the database has been running for a while, we discover that there is a problem with the data table design. Then adjust the structure of the data table, need to do data migration, and may affect the process of business logic, or even the normal service operation of the system.

In fact, in the early stage of database table structure design, we need to pay attention to the design of data table.

1. Database design paradigm

1.1 What are the database design paradigms?

When we design a relational database model, we need to define the rationalization degree of the relationship between various attributes within the relationship, which has different levels of specification requirements, which are called normal form (NF).

A paradigm is simply understood as a level of design criteria that the design structure of a data table needs to meet.

At present, there are altogether 6 paradigms of relational database, which are: 1NF (first paradigm), 2NF (second paradigm), 3NF (third paradigm), BCNF (Bas-Coad paradigm), 4NF (fourth paradigm) and 5NF (fifth paradigm, also known as perfect paradigm).

The higher the order of database paradigm design, the lower the redundancy, and the higher order paradigm must meet the requirements of the lower order paradigm, such as 2NF must meet 1NF, 3NF must meet 2NF, and so on.

So many paradigm levels, do they all have to fit?

In general, data tables should be designed to meet 3NF as far as possible. But not always, sometimes to improve the performance of certain queries, we also need to break the paradigm rules, known as de-normalization.

1.2 Paradigm Design

Formal models are required to meet the following three paradigms:

  • 1NF (First normal Form) means that any field attribute in a database table is atomic and non-divisible

This situation is easier to understand. When we design a field, for field X, we cannot split field X into field X-1 and field X-2.

  • 2NF (second normal form) refers to a table in which non-primary attributes are completely dependent on the candidate key

Suppose there are two data models, user and commodity

The primary key of the user model is the user ID, so the rest of the user model fields should depend on the user ID

The primary key of the product model is the item ID, which has no direct relationship to the user, so this property should not be placed in the user model, but in the order table associated with the “user-item”.

  • 3NF (Third normal Form) model non-primary key fields cannot depend on each other while satisfying 2NF

Example: Order table (order ID, item ID, user ID, user name)

At first glance, the table is fine, meeting the second normal form, and each column is related to the primary key column “Order number”. If you look more closely, you will see that “user name” is associated with “user ID”, “user ID” is associated with “order ID”, and finally, through passing dependencies, “User name” is associated with “order ID”. To satisfy the third normal form, remove the “User name” column from the order table and place it in the user table.

To sum up:

  • 1NF needs to ensure that every attribute in the table remains atomic;
  • 2NF needs to ensure that non-primary attributes in the table are completely dependent on candidate keys;
  • 3NF needs to ensure that there are no transitive dependencies between non-primary attributes and candidate keys in the table.

2. Anti-paradigm design

Although there are many paradigms for data table design, the higher the order of database design paradigms, the finer the data table will be and the less redundant the data will be. To some extent, the database can better organize the data in internal association. In fact, we don’t have to strictly follow these standards when we design our tables, and sometimes we need to use anti-norm optimization, trading space for time.

Since the paradigm is to eliminate redundancy, the anti-paradigm is to improve performance by adding redundancy and aggregation.

Antiparadigms are relative to paradigms, in other words, allowing a small amount of redundancy in exchange for time through space. At the same time, anti-paradigm optimization is also an optimization idea to improve slow query.

For example: the user, item, order table example above

We can redundancy the item name field in the order table, so that we can query the order table through the user association, without having to associate the item table again to get the item name. Example: Order table (Order ID, item ID, user ID, item name)

2.1 Problems of anti-paradigm design

As you can see from the above example, anti-paradigm design can improve query efficiency through space for time, but anti-paradigm can also introduce some new problems.

The main issues include:

  • In the case of small amount of data, anti-paradigm can not reflect the performance advantage, and may make the database design more complicated;
  • For example, using stored procedures to support additional operations such as data update and deletion easily increases the maintenance cost of the system.

2.2 Anti-paradigm design applicable scenarios

So what scenarios does anti-paradigm optimization apply to?

In the real work, we often need some redundant information, such as the consignee information in the order: user name, mobile phone number and the delivery address and so on.

In fact, the receiving information of each order belongs to the historical snapshot information, which needs to be saved. However, users can modify their own information at any time, so it is necessary to save the redundant information.

** Conclusion: ** We can use anti-paradigm design optimization when redundant information is valuable or greatly improves query efficiency.

conclusion

In daily work, we need to comply with certain specifications, such as the project development process, project report and meeting specifications, etc., although these norms will make people feel there is a certain sense of constraints, so we often try to adjust the rules apply, so that we can ensure the correctness and efficiency of work of affordable.

Much like the design specification of a data table, we need to be both normative and easy to implement.

Paradigm design and anti-paradigm design can be called “art” of design because there is no standard answer…

Paradigms themselves are not superior or inferior, only applicable scenarios. There is no perfect design, only the right design, we also need to use a mixture of normal and anti-normal patterns in the design of data tables according to the requirements.

– END –

Author: The path of architecture improvement, focusing on software architecture research, technical learning and personal growth, pay attention to and write me a reply to “01”, send you a programmer growth advanced gift package.

Thanks for reading!