为了报告而对实时数据进行非规范化 - 好还是坏？
对企业应用程序数据库进行非规范化的优点/缺点是什么，因为它将使编写报告变得更容易？
Pro - 在 SSRS 中设计报告可能会“更容易”，因为不需要连接。
由于数据重复和同步，开发/维护应用程序来处理非规范化数据将变得更加困难。
其他的？
What are the pros/cons of de-normalizing an enterprise application database because it will make writing reports easier?
Pro - designing reports in SSRS will probably be "easier" since no joins will be necessary.
Con - developing/maintaining the app to handle de-normalized data will become more difficult due to duplication of data and synchronization.
Others?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
为了报告而进行非规范化是不好的，好吧。
创建视图或非规范化数据仓库都很好。
视图解决了我大部分与报告相关的需求。当用户几乎不断地生成报告或者当您的视图开始变慢时，数据仓库就非常有用。
这就是为什么你想要标准化你的数据库
Denormalization for the sake of reports is Bad, m'kay.
Creating views, or a denormalized data warehouse is good.
Views have solved most of my reporting related needs. Data warehouses are great when users will be generating reports almost constantly or when your views start to slow down.
This is why you want to normalize your database
您应该考虑反规范化的唯一时间是生成报告所需的时间不可接受的时候。反规范化会导致一致性问题，有时无法确定，尤其是在大型数据集中
The only time you should consider de-normaliozation is when the time it takes the report to generate is not acceptable. De-normalization will cause consistentcy issues that are sometimes impossible to determine especially in large datasets
不要仅仅为了消除报告的复杂性而进行非规范化，它可能会导致应用程序的其余部分出现巨大问题。要么您不强制执行导致错误数据的规则，要么如果您这样做，则每个人（而不仅仅是运行报表的两三个人）的插入、删除和更新都会严重减慢。
如果报告确实无法正常运行，则创建一个非规范化的数据仓库，并在每晚或每周的提要中填充它。通常需要此功能的报告类型通常不关心数据是否是最新的，因为它们通常是月度、季度或年度报告，会在事后处理（尤其是汇总）大量数据。
Don't denormalize just to get rid of complexity in reporting, it can cause huge problems in the rest of the application. Either you don't enforce the rules resulting in bad data or if you do then inserts, deletes and updates can be seriously slowed for everyone not just the two or three people who run reports.
If the reports truly can't run well, then create a data warehouse that is denormalized and populate it in a nightly or weekly feed. The kind of reports that typically need this do not generally care if the data is up-to-the minute as they are usually monthly, quarterly, or annual reports that process (and especially aggregate) large amounts of data after the fact.
您可以同时执行这两项操作...让应用程序标准化数据库。
然后为报告创建一个非规范化数据库，并创建一个定期将数据从一个数据库复制到另一个数据库的应用程序。
毕竟，报告并不总是需要拥有最新更新的数据，大多数时候您可以轻松地每 1 小时在报告数据库上启动一次更新，而且每天只需一次。
You can do both... let the normalized database for applications.
Then create a denormalized database for reports, and create an application which regulary copy data from one database to the other.
After all, reports don't always need to have the latest updated data, most of the time you can easily launch an update every 1 hour on the reporting database, and only once a day day.
除了其他答案中提供的数据仓库和视图解决方案（在某些方面都很好）之外，如果您愿意牺牲一些性能来获得最后一秒的数据，但仍然想要一个规范化的数据库，您可以在 Oracle 上使用在提交时具有快速刷新的物化视图，或者在 Sql Server 中，您可以对视图使用聚集索引。
Beyond the data warehouse and views solutions provided in other answers, which are good in some ways, if you are willing to sacrifice some performance to get a good to the last second data, but still want a normalized database, you could use on Oracle a Materialized View with fast refresh on commit, or in Sql Server, you could use clustered indexes for a view.
另一个缺点是数据可能不是实时的，因为数据从标准化形式转变为非标准化形式需要一些时间。如果有人希望报告及时更新到被请求的那一刻，在这种情况下可能很难做到。
如果这是原始帖子中同步的重复，抱歉我不太这么认为。
Another Con is that the data is likely not to be real-time as there is some time moving around the data to go from a normalized form to a de-normalized. If someone wants the report to be up to the very second it was requested, that can be tough to do in this situation.
If this is a duplication of the synchronization in the original post, sorry I didn't quite see it that way.