Overnormalization [英] Overnormalization
问题描述
数据库设计何时被描述为过度规范？这个特征是绝对的吗？还是它依赖于它在应用程序中的使用方式？感谢。
在一般意义上，我认为overnormalized是当你这么多JOIN来检索数据即使在您从索引中调整出来之后，也会对数据库造成显着的性能损失和死锁。显然，对于像MySpace或eBay这样庞大的应用程序和网站，反标准化是一个扩展要求。
作为一些小型企业的开发者，我告诉你，它总是更容易从规范化 - >反规范化，反过来，事实上反过来（避免重复数据，现在业务需求已经改变了一年左右）是更困难的。 / p>
当我阅读一般性声明，例如你应该把地址放在你的客户表中，而不是一个单独的地址表，这样你可以避免加入，我震惊，因为你只是知道一年后，有人会要求你做一些地址，你完全没有预见到，如维护审计线索，或存储多个每个客户。如果您的数据库允许您创建索引视图，您可以回避该问题，直到您的数据集是如此之大，以至于它不可能存在或由一个服务器或一组服务器在一个1-写，多读环境。对于我们大多数人，我不认为这种情况很常发生。
当有疑问时，我瞄准第三个正常形式，有一些例外（例如，字段包含分隔字符串的CSV列表，因为我知道我永远不会从另一个角度查看数据）。当我需要巩固，我会先看我的意见或索引。希望这有帮助。
When would a database design be described as overnormalized? Is this characterization an absolute one? Or is it dependent on the way it is used in the application? Thanks.
In the general sense, I think that overnormalized is when you are doing so many JOINs to retrieve data that it is causing notable performance penalties and deadlocks on your database, even after you've tuned the heck out of your indexes. Obviously, for huge applications and sites like MySpace or eBay, de-normalization is a scaling requirement.
As a developer for several small businesses, I tell you that in my experience it's always been easier to go from normalized -> denormalized than the other way around, and in fact going the other way around (to avoid duplication of data now that the business requirements have changed a year or so later) is much more difficult.
When I read general statements such as "you should put the address in your customers table instead of a separate address table so you can avoid the join", I shudder, because you just know that a year from now somebody's going to ask you to do something with addresses that you totally didn't foresee, like maintaining an audit trail, or storing multiple per customer. If your database allows you to create an indexed view, you can sidestep that issue until you get to the point where your dataset is so large that it can't possibly exist or be served by a single server or set of servers in a 1-write, many-read environment. For most of us, I don't think that scenario happens very often.
When in doubt, I aim for third normal form with some exceptions (for example, having a field contain a CSV-list of separated strings because I know I'll never ever look at the data from the other angle). When I need to consolidate, I'll look at my views or indexes first. Hope this helps.
这篇关于Overnormalization的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
