Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Dynamic Database Schema [closed]

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?

A few possibilities to illustrate:

  • 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

like image 212
Fake Jim Avatar asked Sep 15 '08 20:09

Fake Jim


People also ask

What is dynamic schema in database?

Download Now. Schema design determines the way an application handles its data. With traditional relational databases, you must define your schema before you can add any data. This inflexibility means you can't change your schema as your data, application requirements or business evolves.

Which type of database has a dynamic schema?

Popular dynamic schema databases include HarperDB and MongoDB.

What are the 3 types of database schema?

Schema is of three types: Logical Schema, Physical Schema and view Schema.


2 Answers

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?

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

like image 169
Matt Rogish Avatar answered Oct 05 '22 10:10

Matt Rogish


A strongly typed xml field in MSSQL has worked for us.

like image 28
Mike Becatti Avatar answered Oct 05 '22 10:10

Mike Becatti