不同表中的数据冗余是否不遵循第三范式(3NF)? [英] Does data redundancy in different tables not follow Third Normal Form (3NF)?

问题描述

我有4张桌子.它们每个都包含以下属性:

I have 4 tables. Each of them contain the following attributes:

Table 1 :
 Person (Id (Primary key), Name, Occupation, Location, SecondJob, PerHour, HoursWorked, Phone, Workphone)

Table 2 :
 Job (Id (Foreign key that refers to Person), Title, Name, Location, Salary)

Table 3 :
 SecondJob (Id (Foreign key that refers to Person), Title, Name)

Table 4:
 PhoneNumber (Id (Foreign key that refers to Person), Name, Phone, Workphone)

我可以使用以下伪SQL语句从人"表中获取每个属性的值，例如名称"，标题"，电话"和工作电话":

I can obtain the values of each attribute like Name, Title, Phone and Workphone from the Person table with the following psuedo SQL statement:

Select (ATTRIBUTE NAME) FROM Person WHERE Id IN (PERSONS ID)

1. 是否在不同的表(数据冗余)中重复了某些信息，从而破坏了(即不遵循)第三范式(3NF)?

1. Does the fact that some of the information is being repeated in DIFFERENT TABLES (Data Redundancy), break (ie, not follow) the Third Normal Form (3NF)?

还是应该将这些值分别放入其他表中，并说明该表的主键标识了哪个属性?

Or should the values be put into the other Tables separately and reason what attribute is identifying with the Primary Key of the Table?

我通过从Person获取PerHour和HoursWorked来计算Job中的薪水，然后将它们相乘.我还听说这是多余的数据，因为事实是可以从表中的现有数据推断出数据.

I calculate Salary in Job by getting PerHour and HoursWorked from Person, then multiply them. I have also heard that this is redundant Data, due to the fact that is is data that you could extrapolate from existing Data within the Tables.

但是，这会破坏第三范式吗?

But, does this break the Third Normal Form??

推荐答案

在不同表(数据冗余)中重复信息的事实是否违反了3NF规范?

Does the fact that information is repeated in DIFFERENT TABLES (Data Redundancy), break against 3NF Normalization?

不.表值或变量在给定NF中或不在给定NF中.这独立于任何其他表. (我们还谈到了数据库中所有表都位于该NF中时位于该NF中的情况.)

No. A table value or variable is or isn't in a given NF. This is independent of any other table. (We do also talk about a database being in NF when all of its tables are in that NF.)

可以合理地认为标准化可以消除冗余.但是有很多冗余无法通过规范化解决.而且有很多不错的冗余.并且重复不一定是冗余.仅仅重复 data 并不意味着重复信息".数据在表中的含义取决于表的含义.

Normalization can be reasonably said to remove redundancy. But there is lots of redundancy not addressed by normalization. And there is lots of redundancy that is not bad. And duplication is not necessarily redundancy. Just because data is repeated doesn't mean "information" is repeated. What data says by being or not being in a table depends on the meaning of the table.

但是您似乎认为，仅仅因为在另一个表中复制数据并没有违反3NF，它就没有违反良好设计的其他原则.错了另外，重要的是5NF.使用较低NF的唯一原因是SQL DBMS不能很好地支持5NF.

But you seem to think that just because duplicating data in a different table doesn't violate 3NF that it doesn't violate other principles of good design. That's wrong. Also, it's 5NF that matters. The only reason lower NFs are used is that SQL DBMSs don't support 5NF well.

还是我应该将这些值分别放入其他表中，并说明用表的主键标识的属性是什么?

Or should i just put in the values into the other Tables seperately and reason what attribute is identifying with the Primary Key of the Table?

我猜您想说的是，我是否应该仅将值分别放在一个表中，并通过涉及共享密钥的查询来重建第二个表?即，如果您可以通过查询数据库的其余部分来获取列中的值，那么您应该避免拥有该列吗?一般来说，是的.

I guess you are trying to say, Should I only put the values in one table each and reconstruct the second table via queries involving shared keys? Ie, if you can get the values in a column by querying the rest of the database then should you avoid having that column? Generally speaking, yes.

您的问题假设有误解.这里不是(专有)或"的问题.您应该两者都做.

Your question assumes a misconception. It's not a matter of "(exclusive) or" here. You should do both.

我通过从Person获取PerHour和HoursWorked来计算Job中的薪水，然后将它们相乘.我听说这也是冗余数据，因为它是可以从表中的现有数据中推断出来的数据.

I calculate Salary in Job by getting PerHour and HoursWorked from Person, then multiply them. I heard that this is also redundant Data, due to it being data that you could extrapulate from existing Data in the Tables.

对于数据库的其余部分，这是多余的，因为您可以改用查询.而且，如果您没有适当地限制薪水值，那将是很糟糕的冗余.即使您这样做，列和约束也会使架构复杂化.

It is redundant given the rest of the database, because you could use a query instead. And if you don't constrain salary values appropriately then that is bad redundancy. Even if you do the column and constraint complicate the schema.

但这会破坏3NF规范化吗?

But does it break 3NF Normalization?

否，因为表的NF独立于其他表.但这并不意味着可以.

No, because the NF of a table is independent of other tables. But that doesn't mean it's ok.

(如果您将Salary添加到Person，则新表将不在3NF中.但是，SQL DBMS具有计算列，可以通过将Salary a设置为非3NF表来做到这一点没有它的3NF表的视图.)

(If you added Salary to Person, the new table would not be in 3NF. But then, SQL DBMSs have computed columns that make that ok, by making the non-3NF table with Salary a view of the 3NF table without it.)

了解一些数据库设计方法以及它们如何应用好的设计原理.您的表不必要地解决了应用程序的重叠方面.还可以在编写查询时了解有关JOIN的信息.

Learn some database design method(s) and how they apply principles of good design. Your tables needlessly address overlapping aspects of the application. Also learn about JOIN in writing queries.

这篇关于不同表中的数据冗余是否不遵循第三范式(3NF)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！