Shulou(Shulou.com)05/31 Report--

This article mainly explains "the method of MySQL database model design". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "the method of MySQL database model design".

Design Paradigm

Question: what is a stereotyped design and why do you need a de-normalized design?

The paradigm comes from the English Normal From. In order to design a good database logic relationship in the development process, we must meet certain constraints, which form a development paradigm, which is divided into several levels, and the first level is stricter than the first level.

Meeting these paradigms can theoretically make our database logical structure more concise and clear.

Here are four common paradigms:

The first normal form (1NF)

Second normal form (2NF)

The third normal form (3NF)

Fourth Paradigm (BCNF)

1. The first normal form (1NF)

Each column is an inseparable attribute value, ensuring the atomicity of each column.

The attributes of the two columns are similar or similar or the same. Try to merge the columns with the same attributes to ensure that redundant data is not generated.

The composition of a single attribute as a basic data type

The designed tables are all simple two-dimensional tables.

For example: user receiving address counterexample:

Name, telephone address Zhang San 138000000 Beijing-Chaoyang District-Jiuxianqiao Street

Positive example:

Name telephone province city district street Zhang San 138000000-Jiuxianqiao street, Chaoyang district, Beijing

Summary: each column is an inseparable atomic value (a column can no longer be separated, such as correspondence address and province, city, district)

two。 Second normal form (2NF)

The second paradigm (2NF) is established on the basis of the first paradigm.

The second normal form (2NF) requires that the attributes of the entity are completely dependent on being associated with the primary key. The so-called completion dependency refers to the non-existence and existence of some attributes that depend on keywords, if there is, then this attribute and keyword parts should be separated to form a new entity, the new entity and the original entity is an one-to-many relationship.

Counterexample:

Product ID user ID product name user name purchase quantity time 1001 Microwave oven A102 pockmarked Wang 12022-08-08

Correct example: order form

Product ID user ID purchase quantity order time 100112022-08-08

Product list

Product ID Product name 100Microwave oven A102

User table

User ID user name 1 Wang pockmarked

Summary: eliminate partial functional dependence of columns on primary keys (partial dependence on combined primary keys, such as product ID + user ID as primary key, user name, product name and other primary key dependent fields)

3. The third normal form (3NF)

To meet the third normal form (3NF), we must meet the second normal form (2NF).

The third normal form (3NF) requires that a data table does not contain non-primary key key information that has been contained in other tables, that is, the data cannot have a transitive relationship, that is, each attribute has a direct relationship with the primary key rather than an indirect relationship.

Counterexample:

Order ID user ID product ID product name product manufacturer 11100 Microwave oven A102 Midea 22200 variable Frequency Air Conditioner B101 Haier

Correct example: order form

Order ID user ID Product ID1110022200

Commodity information table

Product ID Product name Product manufacturer 100Microwave oven A102 Meimei 200VF Air Conditioner B101 Haier

Summary: eliminate the delivery dependence of fields on non-primary keys (that is, the need to cancel redundant information such as product names, addresses, etc.) in the order.

II. Stylized design

The definition of the real database specification is very rigorous, such as the definition of the second normal form (2NF). If a relational R term is the first normal form, and each non-principal attribute function is completely dependent on the candidate code, then the relational R belongs to the second normal form.

Conclusion: it is not that the design that fully conforms to the standardization theory is the most perfect design, but depends on the specific business scenarios to repeatedly practice and summarize the most appropriate design.

III. Anti-standardized design

The so-called anti-standardization design is aimed at standardization. 1. Appropriate violation of the requirements of database paradigm design for the sake of performance and reading efficiency; 3. For the performance of the query, some (small amount) of redundant data is allowed. In other words, de-normalization design is to trade space for time directly.

Commodity information

ID Product name Commodity Price Commodity description Commodity Picture address 1 Microwave oven A101 $100.99 Microwave oven tupian.baidu.com that can heat food

Classified information

Classification ID Classification name 1 Electrical Appliances

Corresponding relation table of commodity classification

Commodity ID classification ID11

Anti-standardization design of commodity information

ID Commodity name Category name Commodity Price Commodity description Commodity description Commodity Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.