Re: Continually Changing Table Schema

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

Try this (example is in Oracle):

drop table multiple;

create table multiple (RecordKey integer, FieldID int, Value varchar(16));

insert into multiple values (1, 1, 'rec1field1');

insert into multiple values (1, 2, 'rec1field2');

insert into multiple values (1, 3, 'rec1field3');

insert into multiple values (1, 4, 'rec1field4');

insert into multiple values (2, 1, 'rec2field1');

insert into multiple values (2, 2, 'rec2field2');

insert into multiple values (2, 3, 'rec2field3');

insert into multiple values (2, 4, 'rec2field4');

drop view tableview;

create view tableview as
select t1.recordkey, t1.value as field1, t2.value as field2,  t3.value as field3, t4.value as field4
from multiple t1, multiple t2, multiple t3, multiple t4 where t1.fieldID=1

and t2.fieldID=2
and t3.fieldID=3
and t4.fieldID=4
and t1.recordkey = t2.recordkey
and t1.recordkey = t3.recordkey
and t1.recordkey = t4.recordkey;

select t.recordkey, field1, field2, field3, field4 from tableview t, multiple m
where t.recordkey = m.recordkey
and value='rec2field3';

• to add a new field. The TABLEVIEW view will not see the new fields. insert into multiple select distinct recordkey, (select max(fieldID) from multiple)+1, 'new value' from multiple