File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Virtual Tables and the Like Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Virtual Tables and the Like" Watch "Virtual Tables and the Like" New topic
Author

Virtual Tables and the Like

Yoo-Jin Lee
Ranch Hand

Joined: Nov 01, 2000
Posts: 119
Hi,
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.
-Yoo-Jin
[ July 30, 2003: Message edited by: Yoo-Jin Lee ]
Stuart Friedberg
Greenhorn

Joined: Aug 11, 2003
Posts: 8
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Virtual Tables and the Like