Continually Changing Table Schema

From: Yoo-Jin Lee <yoojin_at_aps-cambridge.co.uk>
Date: Wed, 30 Jul 2003 10:43:53 +0000 (UTC)
Message-ID: <bg87h9$hpn$1_at_titan.btinternet.com>

Hi,
I'm working on a project to allow users to search a database table. The requirement is that users can add an unlimited number of keys (columns) of string or integer data type at anytime. 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.

Here's a Java approach. 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.

Thanks for your thoughts.

-Yoo-Jin Received on Wed Jul 30 2003 - 12:43:53 CEST