数据库设计规范化有多远？ [英] How far to take normalization in database design?

问题描述

我有这些表：

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

（CreatedByID员工的外键）。

("CreatedByID" is a foreign key for employees.)

我有一个查询，我需要运行几乎所有的Web应用程序的请求，抓住办公室中的所有项目。为了消除三个连接，只需向项目添加一个冗余的OfficeID列，这是不好的做法？或者我应该执行以下操作？

And I have a query that I need to run for almost every request of a web application that grabs all projects in an office. 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

直到我注意到性能问题？

Until I notice performance problems?

在应用程序编程中，我总是遵循先用最佳做法和优化后的规则，但是当涉及到数据库设计和正常化这样的时候，我担心，因为数据库管理员总是警告联接成本。

In application programming, I always follow the rule "Write with best practices first and optimize afterwards", but when it comes to database design and normalization like this I get concerned because 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（INSERT / UPDATE / DELETE）的速度较慢

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

它需要更多的空间

对于优化，您可以优化更快的查询或更快的DML（作为规则） ，这两个是对抗者）。

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 为您而设，但在非正规化的情况下，您需要自己编码。如果 Department 移动到另一个 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屋！