动态数据库架构 [英] Dynamic Database Schema
问题描述
为动态逻辑数据库架构提供存储的推荐架构是什么?
What is a recommended architecture for providing storage for a dynamic logical database schema?
澄清:如果系统需要为模型提供存储，该模型的模式可能会在生产中被用户扩展或更改，有哪些好的技术、数据库模型或存储引擎可以实现这一点?
To clarify: Where a system is required to provide storage for a model whose schema may be extended or altered by its users once in production, what are some good technologies, database models or storage engines that will allow this?
说明的几种可能性:
- 通过动态生成的 DML 创建/更改数据库对象
- 创建包含大量稀疏物理列的表，并仅使用重叠"逻辑架构所需的表
- 创建一个长而窄"的表，将动态列值存储为行，然后需要对其进行透视以创建一个短、宽"行集，其中包含特定实体的所有值
- 使用 BigTable/SimpleDB PropertyBag 类型系统
任何基于现实世界经验的答案将不胜感激
Any answers based on real world experience would be greatly appreciated
推荐答案
您提出的建议并不新鲜.很多人都尝试过……大多数人发现他们追求无限"的灵活性，结果却比这少得多.它是数据库设计的蟑螂汽车旅馆"——数据进入，但几乎不可能将其取出.尝试并概念化为任何类型的约束编写代码，你就会明白我的意思.
What you are proposing is not new. Plenty of people have tried it... most have found that they chase "infinite" flexibility and instead end up with much, much less than that. It's the "roach motel" of database designs -- data goes in, but it's almost impossible to get it out. Try and conceptualize writing the code for ANY sort of constraint and you'll see what I mean.
最终结果通常是一个更难调试、维护且充满数据一致性问题的系统.情况并非总是，但通常情况下，这就是结果.主要是因为程序员没有看到这列火车残骸的到来，并且未能防御性地对其进行编码.此外，通常最终的情况是无限"的灵活性确实没有那么必要；这是一种非常糟糕的气味"，当开发团队得到一个规范说天哪，我不知道他们要放什么样的数据，所以让他们放什么"......最终用户就好了拥有他们可以使用的预定义属性类型(编写一个通用电话号码，并让他们创建其中的任何一个——这在一个很好的规范化系统中是微不足道的，并保持灵活性和完整性！)
The end result typically is a system that is MUCH more difficult to debug, maintain, and full of data consistency problems. This is not always the case, but more often than not, that is how it ends up. Mostly because the programmer(s) don't see this train wreck coming and fail to defensively code against it. Also, often ends up the case that the "infinite" flexibility really isn't that necessary; it's a very bad "smell" when the dev team gets a spec that says "Gosh I have no clue what sort of data they are going to put here, so let 'em put WHATEVER"... and the end users are just fine having pre-defined attribute types that they can use (code up a generic phone #, and let them create any # of them -- this is trivial in a nicely normalized system and maintains flexibility and integrity!)
如果您有一个非常优秀的开发团队，并且非常了解您必须通过这种设计克服的问题，那么您就可以成功地编写出一个设计良好、没有严重错误的系统.大多数时候.
If you have a very good development team and are intimately aware of the problems you'll have to overcome with this design, you can successfully code up a well designed, not terribly buggy system. Most of the time.
为什么一开始就对你不利?
Why start out with the odds stacked so much against you, though?
不相信我?谷歌一个真正的查找表"或单表设计".一些好的结果:http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:10678084117056
Don't believe me? Google "One True Lookup Table" or "single table design". Some good results: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:10678084117056
http://thedailywtf.com/Comments/Tom_Kyte_on_The_Ultimate_Extensibility.aspx?pg=3
http://www.dbazine.com/ofinterest/oi-articles/celko22
http://thedailywtf.com/Comments/The_Inner-Platform_Effect.aspx?pg=2
这篇关于动态数据库架构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
