Dynamic Database Schema [closed]

by

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

More Related Contents:

  1. How to implement a many-to-many relationship in PostgreSQL?
  2. How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
  3. What is the purpose of system table master..spt_values and what are the meanings of its values?
  4. How to create multiple one to one’s
  5. What are best practices for multi-language database design? [closed]
  6. Database Structure for Tree Data Structure [closed]
  7. Database Design for Revisions?
  8. A beginner’s guide to SQL database design [closed]
  9. Physical vs. logical (hard vs. soft) delete of database record? [closed]
  10. PostgreSQL: Which Datatype should be used for Currency?
  11. Why (and how) to split column using master..spt_values?
  12. Is there common street addresses database design for all addresses of the world? [closed]
  13. DB design to use sub-type or not?
  14. Should I design a table with a primary key of varchar or int?
  15. Relational database design question – Surrogate-key or Natural-key?
  16. Database EAV Pros/Cons and Alternatives
  17. When to Denormalize a Database Design
  18. Best way to model Customer Address
  19. MySQL – how to front pad zip code with “0”?
  20. SQL ON DELETE CASCADE, Which Way Does the Deletion Occur?
  21. PostgreSQL Index on JSON
  22. SQL: Using NULL values vs. default values
  23. Primary key/foreign Key naming convention [closed]
  24. Why & When should I use SPARSE COLUMN? (SQL SERVER 2008)
  25. What is the best way to implement soft deletion?
  26. Relational Data Model for Double-Entry Accounting
  27. Is this a good way to model address information in a relational database?
  28. Temporal database design, with a twist (live vs draft rows)
  29. Historical / auditable database
  30. Single or multiple databases

Leave a Comment