动态数据库架构 [英] 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键入系统
- Creating/altering database objects via dynamically generated DML
- Creating tables with large numbers of sparse physical columns and using only those required for the 'overlaid' logical schema
- Creating a 'long, narrow' table that stores dynamic column values as rows that then need to be pivoted to create a 'short, wide' rowset containing all the values for a specific entity
- Using a BigTable/SimpleDB PropertyBag type system
任何基于真实世界体验的答案将不胜感激
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.
最终结果通常是更难以调试的系统，维护和充满数据一致性问题。这不是总是的情况，但通常情况下，这是最终的结果。主要是因为程序员没有看到这列火车残骸来了，没有防守防范。而且，经常会出现这样的情况：无限的灵活性并不是那么必要;这是一个非常糟糕的气味，当开发团队得到一个规范，说我不知道他们要放在这里什么样的数据，所以让我们把WHATEVER ... ...最终用户只是罚款具有他们可以使用的预定义的属性类型（编码通用电话＃，并让他们创建任何一个 - 这在一个很好的标准化系统中是微不足道的，并保持灵活性和完整性！）
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?
不相信我？ GoogleOne True Lookup Table或单表设计。一些好的结果：
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
< a href =http://thedailywtf.com/Comments/Tom_Kyte_on_The_Ultimate_Extensibility.aspx?pg=3 =noreferrer> 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屋！
