MYSQL 中的规范化

Normalization in MYSQL

提问by Fero

Could anybody help me to know what is normalization in mysql and in which case and how we need to use it..

任何人都可以帮我知道什么是 mysql 中的规范化，在这种情况下以及我们需要如何使用它..

Thanks in advance.

提前致谢。

回答by Extrakun

I try to attempt to explain normalization in layman terms here. First off, it is something that applies to relational database (Oracle, Access, MySQL) so it is not only for MySQL.

我试图在这里用外行的术语解释规范化。首先，它适用于关系数据库（Oracle、Access、MySQL），因此它不仅适用于 MySQL。

Normalisation is about making sure each table has the only minimal fields and to get rid of dependencies. Imagine you have an employee record, and each employee belongs to a department. If you store the department as a field along with the other data of the employee, you have a problem - what happens if a department is removed? You have to update all the department fields, and there's opportunity for error. And what if some employees does not have a department (newly assigned, perhaps?). Now there will be null values.

规范化是关于确保每个表只有最少的字段并摆脱依赖关系。假设您有一个员工记录，每个员工都属于一个部门。如果您将部门与员工的其他数据一起存储为一个字段，则会出现问题 - 如果删除部门会发生什么？您必须更新所有部门字段，并且可能会出错。如果某些员工没有部门（可能是新分配的？），该怎么办？现在会有空值。

So the normalisation, in brief, is to avoid having fields that would be null, and making sure that the all the fields in the table only belong to one domain of data being described. For example, in the employee table, the fields could be id, name, social security number, but those three fields have nothing to do with the department. Only employee id describes which department the employee belongs to. So this implies that which department an employee is in should be in another table.

因此，简而言之，规范化是为了避免字段为空，并确保表中的所有字段仅属于所描述的一个数据域。例如，在employee表中，字段可能是id、姓名、社会保险号，但这三个字段与部门无关。只有员工 ID 描述了员工所属的部门。所以这意味着员工所在的部门应该在另一个表中。

Here's a simple normalization process.

这是一个简单的规范化过程。

EMPLOYEE ( < employee_id >, name, social_security, department_name)

This is not normalized, as explained. A normalized form could look like

正如所解释的，这不是标准化的。规范化的形式可能看起来像

EMPLOYEE ( < employee_id >, name, social_security)

Here, the Employee table is only responsible for one set of data. So where do we store which department the employee belongs to? In another table

这里，Employee 表只负责一组数据。那么我们在哪里存储员工属于哪个部门呢？在另一个表

EMPLOYEE_DEPARTMENT ( < employee_id >, department_name )

This is not optimal. What if the department name changes? (it happens in the US government all the time). Hence it is better to do this

这不是最优的。如果部门名称发生变化怎么办？（它一直发生在美国政府中）。因此最好这样做

EMPLOYEE_DEPARTMENT ( < employee_id >, department_id )
DEPARTMENT ( < department_id >, department_name )

There are first normal form, second normal form and third normal form. But unless you are studying a DB course, I usually just go for the most normalized form I could understand.

有第一范式、第二范式和第三范式。但除非你正在学习 DB 课程，否则我通常只会选择我能理解的最规范的形式。

Hope this helps.

希望这可以帮助。

回答by rahul

Normalization is not for MYSql only. Its a general database concept.

规范化不仅仅适用于 MYSql。它是一个通用的数据库概念。

Normalization is the process of efficiently organizing data in a database. There are two goals of the normalization process: eliminating redundant data (for example, storing the same data in more than one table) and ensuring data dependencies make sense (only storing related data in a table). Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored.

规范化是在数据库中有效组织数据的过程。规范化过程有两个目标：消除冗余数据（例如，将相同数据存储在多个表中）和确保数据依赖性有意义（仅将相关数据存储在一个表中）。这两个都是有价值的目标，因为它们减少了数据库消耗的空间量并确保数据在逻辑上存储。

Normal forms in SQL are given below.

SQL 中的范式如下所示。

First Normal form (1NF): A relation is said to be in 1NF if it has only single valued attributes, neither repeating nor arrays are permitted.

Second Normal Form (2NF): A relation is said to be in 2NF if it is in 1NF and every non key attribute is fully functional dependent on the primary key.

Third Normal Form (3NF): We say that a relation is in 3NF if it is in 2NF and has no transitive dependencies.

Boyce-Codd Normal Form (BCNF): A relation is said to be in BCNF if and only if every determinant in the relation is a candidate key.

Fourth Normal Form (4NF): A relation is said to be in 4NF if it is in BCNF and contains no multivalued dependency.

Fifth Normal Form (5NF): A relation is said to be in 5NF if and only if every join dependency in relation is implied by the candidate keys of relation.

Domain-Key Normal Form (DKNF): We say that a relation is in DKNF if it is free of all modification anomalies. Insertion, Deletion, and update anomalies come under modification anomalies

第一范式（1NF）：如果一个关系只有单值属性，不允许重复或数组，则称其为 1NF。

第二范式 (2NF)：如果一个关系在 1NF 中并且每个非键属性都完全依赖于主键，则称该关系在 2NF 中。

第三范式（3NF）：如果一个关系在 2NF 中并且没有传递依赖关系，我们就说它在 3NF 中。

Boyce-Codd 范式 (BCNF)：当且仅当关系中的每个行列式都是候选键时，才称该关系属于 BCNF。

第四范式（4NF）：如果一个关系在 BCNF 中并且不包含多值依赖，则称该关系在 4NF 中。

第五范式（5NF）：当且仅当关系中的每个连接依赖都由关系的候选键隐含时，关系才称为 5NF。

域键范式（DKNF）：如果一个关系没有所有修改异常，我们就说它在 DKNF 中。插入、删除和更新异常属于修改异常

Seel also

也见

Database Normalization Basics

数据库规范化基础

回答by RichieHindle

It's a technique for ensuring that your data remains consistent, by eliminating duplication. So a database in which the same information is stored in more than one table is not normalized.

这是一种通过消除重复来确保数据保持一致的技术。因此，将相同信息存储在多个表中的数据库是不规范化的。

See the Wikipedia article on Database normalization.

请参阅有关数据库规范化的维基百科文章。

(It's a general technique for relational databases, not specific to MySQL.)

（这是关系数据库的通用技术，并非特定于 MySQL。）

回答by Narayan

check thispost has helpful suggestions

检查此帖子是否有有用的建议

Barry's Tutorial on understanding a Database Schema

Barry 关于理解数据库模式的教程

http://www.youtube.com/watch?v=KqvIGYjcLQ4 

回答by a6hi5h3k

While creating a database schema for your application, you need to make sure that you avoid any information being stored in more than one column across different tables.

在为您的应用程序创建数据库模式时，您需要确保避免将任何信息存储在跨不同表的多个列中。

As every table in your DB, identifies a significant entity in your application, a unique identifier is a must-have columns for them.

由于数据库中的每个表都标识应用程序中的一个重要实体，因此唯一标识符是它们必备的列。

Now, while deciding the storage schema, various kinds of relationships are being identified between these entities (tables), viz-a-viz, one-to-one, one-to-many, many-to-many.

现在，在决定存储模式时，正在识别这些实体（表）之间的各种关系，即一对一、一对多、多对多。

1. For a one-to-one relationship (eg. A Student has a unique rank in the class), same table could be used to store columns (from both tables).
2. For a one-to-many relationship (eg. A semester can have multiple courses), a foreign key is being created in a parent table.
3. For a many-to-many relationship (eg. A Prof. attends to many students and vice-versa), a third table needs to be created (with primary key from both tables as a composite key), and related data of the both tables will be stored.

1. 对于一对一的关系（例如，学生在班级中具有唯一的排名），可以使用同一个表来存储列（来自两个表）。
2. 对于一对多关系（例如，一个学期可以有多个课程），在父表中创建外键。
3. 对于多对多的关系（例如，一个教授照顾很多学生，反之亦然），需要创建第三张表（两个表的主键作为组合键），以及两者的相关数据表将被存储。

Once you attend to all these scenarios, your db-schema will be normalized to 4NF.

一旦您处理了所有这些场景，您的 db-schema 将被规范化为 4NF。

回答by a6hi5h3k

In the field of relational database design, normalization is a systematic way of ensuring that a database structure is suitable for general-purpose querying and free of certain undesirable characteristics—insertion, update, and deletion anomalies—that could lead to a loss of data integrity.[1] E.F. Codd, the inventor of the relational model, introduced the concept of normalization and what we now know as the first normal form in 1970.[2] Codd went on to define the second and third normal forms in 1971,[3] and Codd and Raymond F. Boyce defined the Boyce-Codd normal form in 1974.[4] Higher normal forms were defined by other theorists in subsequent years, the most recent being the sixth normal form introduced by Chris Date, Hugh Darwen, and Nikos Lorentzos in 2002.[5]

Informally, a relational database table (the computerized representation of a relation) is often described as "normalized" if it is in the third normal form (3NF).[6] Most 3NF tables are free of insertion, update, and deletion anomalies, i.e. in most cases 3NF tables adhere to BCNF, 4NF, and 5NF (but typically not 6NF).

A standard piece of database design guidance is that the designer should create a fully normalized design; selective denormalization can subsequently be performed for performance reasons.[7] However, some modeling disciplines, such as the dimensional modeling approach to data warehouse design, explicitly recommend non-normalized designs, i.e. designs that in large part do not adhere to 3NF.[8]

在关系数据库设计领域，规范化是一种系统方法，可确保数据库结构适用于通用查询，并且没有某些不良特征——插入、更新和删除异常——可能导致数据完整性丢失.[1] 关系模型的发明者 EF Codd 在 1970 年引入了规范化的概念以及我们现在所知的第一个范式。 [2] Codd 在 1971 年继续定义了第二和第三范式，[3] 而 Codd 和 Raymond F. Boyce 在 1974 年定义了 Boyce-Codd 范式 [4]。随后几年其他理论家定义了更高的范式，最近的是 Chris Date、Hugh Darwen 和 Nikos Lorentzos 在 2002 年引入的第六范式。 [5]

非正式地，如果关系数据库表（关系的计算机化表示）处于第三范式（3NF）中，则通常将其描述为“规范化”。 [6] 大多数 3NF 表都没有插入、更新和删除异常，即在大多数情况下，3NF 表遵守 BCNF、4NF 和 5NF（但通常不是 6NF）。

一个标准的数据库设计指南是设计者应该创建一个完全规范化的设计；出于性能原因，可以随后执行选择性非规范化。 [7] 然而，一些建模学科，例如数据仓库设计的维度建模方法，明确推荐非规范化设计，即在很大程度上不遵守 3NF 的设计。[8]

Edit:Source: http://en.wikipedia.org/wiki/Database_normalization

编辑：来源：http: //en.wikipedia.org/wiki/Database_normalization