在这种情况下是否需要归一化？ [英] Is normalization required in this case?

问题描述

假设我有一个数据库，表格如下：

  id | fundraiser_id | donation_amount |名称|性别|大学
  

这是我真正的表格的类似版本。此表在筹款活动中追踪捐款。同一个人很可能会为每个筹款人多次捐款（他们非常慷慨）。

用户可以像电子表格一样输入这些数据。他们不一定关心他们进入同一个名字，性别和大学多行。毕竟，他们不支付存储空间。

我的问题是：我应该通过尝试在表更新来提取不同的值名称，性别，大学并保存在一个人的桌子。然后，我的新表将如下所示：

  id | fundraiser_id | donation_amount | people_id 
  

让用户预定义他们将在表中使用的人不是一个选项。 >

我讨论了这种情况在前一个问题，但我觉得我没有给足够的细节。以下是我（以及其他帮助我的上一个问题）的优缺点：

优点：

• 减少浪费的空间


• 更多正常


• 更快回答一些查询

缺点：

• 获取资源以确保我不向People表中添加重复的人


• 将使用Cron作业来杀死People表中的孤立条目
• 
  
• 无论我如何查看，都需要额外的查询

感谢您的任何建议。 p>

再说一遍：

我并不关心两个人具有相同的名字，性别和大学。这些信息只是显示而不是采取行动，所以两个不同的人仍然是我的应用程序。我只是建议这种规范化是通过创建一个查找表来节省空间的一种方法。

解决方案

样本数据。假设列id是主键，并且人们不会产生拼写错误。

 表：捐款
 id fundraiser_id donation_amount name sex university 
  -  
 1 100 $ 100 Kim Stack M Rivier College 
 2 100 $ 150 Kim Stack M Rivier College 
 3 100 $ 45 Marguerite Meade F Rivier学院
 4 100 $ 100 Marie Dew F Rivier学院
 5 100 $ 100 Kim Stack F中途学院
 6 100 $ 100 Kim Stack F Mars Hill College 
 ... 
 98 200 $ 135 Kim Stack M Rivier College 
 99 200 $ 400 Kim Stack M Midway College 
  

找到与人有交往的依赖关系。

• name-> sex：不，有男性和女性名为Kim Stack


• name->大学：No。


• 名称，性别 - >大学：不，两名名为Kim Stack的女性去不同的大学。


• 性别，大学 - >姓名：不，Rivier学院有两个不同的女性。


• 名称，大学 - >性别：不，在中途学院有一名名为Kim Stack的男女学生。

（你可以继续下去）

这个表没有传递依赖关系，id是唯一的候选键：已经在5NF。

尽可能有用，用id号替换名字或用id号替换{name，sex，university}的名称没有任何内容与规范化有关。

Let's say I have a database with a table like this:

id | fundraiser_id | donation_amount | name | sex | university

This is an analogous version of my real table. This table tracks donations during a fundraiser. It is very likely that the same person will donate multiple times for each fundraiser (they are very generous).

A user would enter this data like a spreadsheet. They won't necessarily care that they entering the same name, sex, and university for multiple rows. After all, they aren't paying for the storage space.

My question is this: Should I normalize this table by attempting, on table update, to extract distinct values for the set of name, sex, university and save them in a People table. Then, my new table would look like this:

id | fundraiser_id | donation_amount | people_id

Having the user predefine the people they will use in their table is not an option.

I discussed this situation in a previous question, but I felt that I didn't give enough detail. Here are the pros and cons that I (and the others that helped me in that previous question) came up with:

Pros:

• Less wasted space
• More "normal"
• Faster to answer some queries

Cons:

• Takes resources to ensure that I don't add duplicate people to the People table
• Will take a Cron job to kill orphaned entries in the People table
• Extra queries required no matter how I look at it

Thank you for any advice.

Just one more clarification:

I'm not concerned at all about two people having the same name, sex, and university. That information is just displayed and not acted on, so two different people are still one in the same to my application. I am only suggesting this normalization as a way to save space by creating a lookup table.

解决方案

Let's try to put together some reasonable sample data. Assume that the column 'id' is the primary key, and that people don't make spelling mistakes.

Table: donations
id   fundraiser_id   donation_amount   name              sex   university
--
1    100             $100              Kim Stack         M     Rivier College
2    100             $150              Kim Stack         M     Rivier College
3    100             $45               Marguerite Meade  F     Rivier College
4    100             $100              Marie Dew         F     Rivier College
5    100             $100              Kim Stack         F     Midway College
6    100             $100              Kim Stack         F     Mars Hill College
...
98   200             $135              Kim Stack         M     Rivier College
99   200             $400              Kim Stack         M     Midway College

Let's try to find a transitive dependency having to do with people.

• name->sex: No, there are males and females named "Kim Stack".
• name->university: No.
• name, sex->university: No, two females named "Kim Stack" go to different colleges.
• sex, university->name: No, there are two different females at Rivier College.
• name, university->sex: No, there's a male and female student named "Kim Stack" at Midway College.

(You can keep going.)

This table has no transitive dependencies, and "id" is the only candidate key: this table is already in 5NF.

As useful as it might be, replacing a name with an id number—or replacing {name, sex, university} with an id number—has nothing to do with normalization.

这篇关于在这种情况下是否需要归一化？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！