当前位置： 文江博客话题详情

MYSQL 中的规范化

发布于 2024-08-01 16:28:44 字数 36 浏览 3 评论 0原文

MySQL 中的规范化是什么？在什么情况下以及如何使用它？

原文

What is normalization in MySQL and in which case and how we need to use it?

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（5）

挥剑断情 2024-08-08 16:28:44

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

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

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

这是一个简单的标准化过程。

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

正如所解释的，这没有标准化。 规范化形式可能如下所示

EMPLOYEE ( < employee_id >, name, social_security)

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

EMPLOYEE_DEPARTMENT ( < employee_id >, department_name )

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

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

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

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.

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.

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_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.

回复 原文
∞觅青森が 2024-08-08 16:28:44

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

标准化的过程是
有效地组织数据
数据库。 该组织有两个目标
标准化过程：消除
冗余数据（例如，存储
多个表中的相同数据）
并确保数据依赖性
sense（仅将相关数据存储在
桌子）。 这两个都是有价值的目标
因为它们减少了空间量
数据库消费并保证数据
逻辑存储。

SQL 中的范式如下所示。

第一范式 (1NF)：关系是
如果它只有
单值属性，两者都不是
不允许重复或数组。

第二范式 (2NF)：关系
如果它在 1NF 中，则称其在 2NF 中
并且每个非关键属性都完全
功能依赖于初级
关键。

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

Boyce-Codd 范式 (BCNF)：A
关系被认为是在 BCNF 中，如果并且
仅当其中的每个行列式
关系是候选键。

第四范式 (4NF)：关系
如果它在 BCNF 中，则称其在 4NF 中
并且不包含多值依赖。

第五范式（5NF）：关系是
称为 5NF 当且仅当每个
隐含了关系中的连接依赖性
通过关系的候选键。

域名密钥范式 (DKNF)：我们说
一个关系在 DKNF 中，如果它是
没有任何修改异常。
插入、删除和更新
异常情况正在修改
异常

另请参阅

数据库规范化基础知识

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

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.

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

Seel also

Database Normalization Basics

回复 原文
倥絔 2024-08-08 16:28:44

这是一种通过消除重复来确保数据保持一致的技术。 因此，如果数据库中相同的信息存储在多个表中，那么该数据库就不是标准化的。

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

（这是关系数据库的通用技术，不是 MySQL 特有的。）

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.)

回复 原文
以可爱出名 2024-08-08 16:28:44

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

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

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

  1. 对于一对一的关系（例如 A
    学生在大学中拥有独特的排名
    类），同一个表可用于
    存储列（来自两个表）。
  2. 对于一对多关系（例如
    一个学期可以有多个
    课程），外键正在
    在父表中创建。
  3. 对于多对多关系（例如
    一位教授照顾许多学生并且
    反之亦然），第三个表需要
    被创建（主键来自
    两个表都作为复合键），以及
    两个表的相关数据将
    被存储。

一旦您处理了所有这些场景，您的数据库模式将标准化为 4NF。

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.

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

回复 原文
无语# 2024-08-08 16:28:44

在关系数据库领域
设计、规范化是一个系统化的过程
确保数据库的方法
结构适用于
通用查询并且免费
某些不受欢迎的
特征——插入、更新和
删除异常——这可能会导致
数据完整性丢失。[1] EF
Codd，关系理论的发明者
模型，引入了概念
正常化以及我们现在所知道的
1970年的第一个范式。[2] 科德
继续定义第二个和第三个
1971 年的范式，[3] 以及 Codd 和
雷蒙德·博伊斯 (Raymond F. Boyce) 定义了
Boyce-Codd 范式，1974 年。[4]
更高范式定义为
随后几年的其他理论家，
最近的是第六常态
由 Chris Date、Hugh 提出的形式
达尔文和尼科斯·洛伦佐斯
2002.[5]

通俗地说，关系数据库
表（计算机化表示
关系的）通常被描述为
如果是第三个则“标准化”
范式（3NF）。[6] 大多数 3NF 表
免于插入、更新和
删除异常，即大多数情况下
3NF 表遵循 BCNF、4NF 和
5NF（但通常不是 6NF）。

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

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

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]

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

回复 原文
~没有更多了~

关于作者

美男兮

暂无简介

0 文章
0 评论
21 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

不再见

文章 0 评论 0

真是无聊啊

文章 0 评论 0

樱娆

文章 0 评论 0

浅语花开

文章 0 评论 0

烛光

文章 0 评论 0

绻影浮沉

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文