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

问题描述

一位前同事坚持认为，一个表多但列少的数据库比表少但列多的数据库要好.例如，您将拥有姓名表、地址表、城市表等，而不是包含姓名、地址、城市、州、邮政编码等列的客户表.

他认为这种设计更高效、更灵活.也许它更灵活，但我没有资格评论它的效率.即使它更有效，我认为这些收益可能会被增加的复杂性所抵消.

那么，与具有更多列的更少表相比，更多列更少的表有什么显着的好处吗?

解决方案

我在设计数据库时遵循了一些相当简单的经验法则，我认为它们可以用来帮助做出这样的决策......

>

1. 赞成规范化.非规范化是一种优化形式，具有所有必要的权衡，因此应该使用 YAGNI 态度.
2. 确保引用数据库的客户端代码与架构充分解耦，从而无需对客户端进行重大重新设计.
3. 如果非规范化对性能或查询复杂性有明显的好处，请不要害怕.
4. 在数据量和使用场景允许的情况下，使用视图或下游表来实现非规范化，而不是对架构的核心进行非规范化.

这些规则的通常结果是，最初的设计倾向于表而不是列，重点是消除冗余.随着项目的进展和非规范化点的确定，整体结构将朝着平衡发展，即以有限的冗余和列扩散来换取其他有价值的好处.

A former coworker insisted that a database with more tables with fewer columns each is better than 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屋！