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 ]
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.