数据库规范化 [英] normalization in database

问题描述

我有一张桌子。如何正常化这个。

< img src =https://i.stack.imgur.com/IoDGT.jpgalt =enter image description here>

解决方案

给出您的数据集的好问题。请记住，整个归档点是减少重复。 3NF通常是最好的方式。但是鉴于我的经验，我发现非常很少有利于将重复的值提取到不同的表中，如果它是该表中唯一的值。举个例子，你最重复的列，emp_type。如果你把它规范化成一个单独的表格，它将如下所示：

  Emp_Type_Id | Emp_type 
 ------------------------ 
 1 |经理
 2 |工程师
 3 |技术支持
  

您当前的表格如下所示：

  Emp_ID | Emp_Name |工资| Emp_Type_Id | Emp_Skill 
 --------------------------------------------- ------- 
 1 | raj | 90000 | 1 | Department 
 2 | ravi | 50000 | 2 |软件
 3 | shyam | 70000 | 2 |硬件
。 
。 
。 
  

这在技术上比以前更规范化，因为在数据库中不再重复emp_type值。但处理单独表格和单一价值的关系显得更麻烦。如果emp_type还包括其他信息，例如Valid_Salary_Range，Department_Location等，那么绝对最好将其规范化为单独的表。但是，如果它只是一个单一的值正规化，存储值而不是存储指向该值的ID有什么区别？在我看来，这是一个无意义的额外步骤。

长篇小说，我根本不会使你的表正常化，它已经规范化到一个足够的水平。 b $ b

I have one table. How can I normalize this.

解决方案

Good question given your data set. Keep in mind the whole point of normalization is to reduce duplication. 3NF is often the best way to go. But given my experience I've found very little benefit to pulling a repeated value out into a different table if it will be the only value in that table. Take for example your most duplicated column, emp_type. If you were to normalize it into a separate table , it would look like this:

Emp_Type_Id | Emp_type
------------------------
1           | Manager
2           | Engineer
3           | Tech Support

And your current table would look like:

Emp_ID | Emp_Name | Salary | Emp_Type_Id | Emp_Skill
----------------------------------------------------
1      | raj      | 90000  | 1           | Department
2      | ravi     | 50000  | 2           | Software
3      | shyam    | 70000  | 2           | Hardware
.
.
.

This is technically more normalized than before as the emp_type value is no longer repeated in your database. But it's significantly more hassle to deal with a separate table and a relationship for a single value. If emp_type included other information as well such as Valid_Salary_Range, Department_Location, etc. then it would absolutely be best to normalize it out into a separate table. But if it's only a single value you are normalizing, what's the difference between storing the value as opposed to storing an ID pointed to that value? In my opinion it's a pointless extra step.

Long story short, I would not normalize your table at all, it's already normalized to a sufficient level.

这篇关于数据库规范化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！