什么是更好的数据库设计：更多的表或更多的列？ [英] What's the better database design: more tables or more columns?

问题描述

前同事坚持认为，具有更多表格的数据库（每列具有更少的列）更好，而具有更少表格（每列具有更多列）的数据库更好。例如，不是具有姓名，地址，城市，州，邮编等的列的客户表，您将具有名称表，地址表，城市表等。

$ b $他认为这种设计更有效率和灵活性。也许它更灵活，但我不能评论其效率。即使效率更高，我认为这些收益可能会因增加的复杂性而超过。

因此，对于更少的列，更少的表，更多的表有明显的好处更多列？

解决方案

我有一些相当简单的经验规则，当我设计数据库时，我认为可以使用以帮助作出这样的决定....

1. 喜欢归一化。非正规化是一种具有所有必要权衡的优化形式，因此应通过 YAGNI 态度。


2. 确保引用数据库的客户端代码与解决方案足够分离，不需要重新设计客户端。


3. 使用视图或下游表实现非规范化，而不是反规范化

通常的结果是，当数据量和使用情况允许时，这些规则是，初始设计将优先于列，而重点是消除冗余。随着项目的进展和非正规化点的确定，整体结构将朝着一种平衡的方向发展，这种平衡与有限的冗余和列扩散相互作用以换取其他有价值的好处。

A former coworker insisted that a database with more tables with fewer columns each is better then one with fewer tables with more columns each. For example rather than a customer table with name, address, city, state, zip, etc. columns, you would have a name table, an address table, a city table, etc.

He argued this design was more efficient and flexible. Perhaps it is more flexible, but I am not qualified to comment on its efficiency. Even if it is more efficient, I think those gains may be outweighed by the added complexity.

So, are there any significant benefits to more tables with fewer columns over fewer tables with more columns?

解决方案

I have a few fairly simple rules of thumb I follow when designing databases, which I think can be used to help make decisions like this....

1. Favor normalization. Denormalization is a form of optimization, with all the requisite tradeoffs, and as such it should be approached with a YAGNI attitude.
2. Make sure that client code referencing the database is decoupled enough from the schema that reworking it doesn't necessitate a major redesign of the client(s).
3. Don't be afraid to denormalize when it provides a clear benefit to performance or query complexity.
4. Use views or downstream tables to implement denormalization rather than denormalizing the core of the schema, when data volume and usage scenarios allow for it.

The usual result of these rules is that the initial design will favor tables over columns, with a focus on eliminating redundancy. As the project progresses and denormalization points are identified, the overall structure will evolve toward a balance that compromises with limited redundancy and column proliferation in exchange for other valuable benefits.

这篇关于什么是更好的数据库设计：更多的表或更多的列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！