标准化要走多远？ [英] How far to take normalization?

问题描述

我有这些表：

Projects(projectID, CreatedByID)
Employees(empID,depID)
Departments(depID,OfficeID)
Offices(officeID)

CreatedByID 是的外键员工。我有一个查询，几乎所有页面加载都运行。

CreatedByID is a foreign key for Employees. I have a query that runs for almost every page load.

仅添加冗余的 OfficeID 是不好的做法吗？列到 Projects 来消除三个联接？还是应该执行以下操作：

Is it bad practice to just add a redundant OfficeID column to Projects to eliminate the three joins? Or should I do the following:

SELECT * 
FROM Projects P
JOIN Employees E   ON P.CreatedBY = E.EmpID
JOIN Departments D ON E.DepID = D.DepID
JOIN Offices O     ON D.officeID = O.officeID
WHERE O.officeID = @SomeOfficeID

在应用程序编程中，我先写最佳实践，然后再进行优化，但是数据库管理员始终会警告

In application programming I "Write with best practices first and optimize afterwards", but database administrators are always warning about the cost of joins.

推荐答案

反规范化的优点是可以快速 SELECT

Denormalization has the advantage of fast SELECTs on large queries.

缺点是：

• 它需要更多的编码和确保完整性的时间（这对您而言是最重要的）

• It takes more coding and time to ensure integrity (which is most important in your case)

在DML（插入/更新/删除）上速度较慢

It's slower on DML (INSERT/UPDATE/DELETE)

需要更多空间

对于优化，您可以针对更快的查询速度或更快的DML（通常，这两个是antag

As for optimization, you may optimize either for faster querying or for faster DML (as a rule, these two are antagonists).

为加快查询速度而进行的优化通常意味着重复数据，无论是非规范化，索引还是其他任何形式的表。

Optimizing for faster querying often implies duplicating data, be it denormalization, indices, extra tables of whatever.

在使用索引的情况下， RDBMS 可以帮您实现，但是在非规范化的情况下，您需要自己编写代码。如果部门转移到另一个 Office ，该怎么办？您需要在三个表中而不是一个表中进行修复。

In case of indices, the RDBMS does it for you, but in case of denormalization, you'll need to code it yourself. What if Department moves to another Office? You'll need to fix it in three tables instead of one.

因此，正如我从表名中看到的那样，那里不会有数百万条记录。因此，最好将数据归一化，这将更易于管理。

So, as I can see from the names of your tables, there won't be millions records there. So you'd better normalize your data, it will be simplier to manage.

这篇关于标准化要走多远？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！