Re: Continually Changing Table Schema

>> Initially, my thoughts were to create a table (tblMyData) with one
column for the unique identifier. The user then adds columns of varchar or integer. An bmp ejb could model the metadata of tblMyData so that a search string could be constructed. jdbc would be used to directly access tblMyData. <<

You think like an OO programmer - you even put prefixes that descirbe the PHYSICAL storage method on data element names! That "tbl-" looks redudant and dangerous to a relational programmer.

>> Is this bad design? Thoughts? Anyother way to do this? <<

Very bad; it falls apart in about year.

Many years ago, the INCITS H2 Database Standards Committee(nee ANSI X3H2 Database Standards Committee) had a meeting in Rapid City, South Dakota. We had Mount Rushmore and Bjarne Stroustrup as special attractions. Mr. Stroustrup did his slide show about Bell Labs inventing C++ and OO programming for us and we got to ask questions.

One of the questions was how we should put OO stuff into SQL. His answer was that Bells Labs, with all their talent, had tried four different approaches to this problem and come the conclusion that you should not do it. OO was great for programming but deadly for data.

I have watched people try to force OO models into SQL and it falls apart in about a year. Every typo becomes a new attribute or class, queries that would have been so easy in a relational model are now mutli-table monster outer joins, redundancy grows at an exponential rates, constraints are vitually impossible to write so you can kiss data integrity goodbye, etc.

Since the user has no idea what they are doing, you cannot help them. I would refuse the job, or tell them that this is a document and textbase problem, not a database problem. Received on Thu Jul 31 2003 - 01:14:50 CEST