• Post Reply Bookmark Topic Watch Topic
  • New Topic

Virtual Tables and the Like  RSS feed

Yoo-Jin Lee
Ranch Hand
Posts: 119
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm working on a project that lets the user effectively add a column of an integer or varchar to a table in any SQL 92 complaint database anytime throughout the life of the project. Then I want to run a query against data that is entered into this table. We had a consultant visit who recommended against modelling the project on a database schema that constantly changes. His reasons were due to future updates to the project. I can't think of any other way of doing it.
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.
Is this bad design? Thoughts? Anyother way to do this?
I've read of virtual tables and which lets you describe the meta data in separate tables to finally come up with an overall view but I can't see how the final view can be created using pure sql 92 or even programmatically.
Thanks for your thoughts.
[ July 30, 2003: Message edited by: Yoo-Jin Lee ]
Stuart Friedberg
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Without knowing more about your situation, I have to agree with the
consultant. Allowing end users to add new attributes to existing
tables at arbitrary times is just asking for a big mess. If you
can say, what's the general application and where is the data for
old rows in the new columns going to come from?
Having said that, if your database supports views (most non-enterprise
DBMS's don't), you define a view using the same features as SELECT.
Suppose you have an original table and then create new "auxiliary"
tables when an enduser decides to add some new attributes. It's
tedious, but not hard, to concatenate all the columns of all the
tables into a single SELECT X.col1, X.col2, Y.col1, Z.col1 when
defining the view. You would have to redefine the view whenever the
tables change. The view can then be treated just like a (usually
read-only) table in further SELECTs.
But this doesn't change the fact that your schema can evolve into
a mess. Using a view to concatenate separate tables is probably a
bit safer than adding columns to existing tables on the fly, and it
does let a DBM review changes for sanity when redefining the view.
But you still have a mess unless there are some unstated constraints
on when and what the end users are doing with those new columns.
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!