Ed,

Thanks for replying.

I have not misinterpreted the requirement as stated. Unfortunately, being a
developer you have to code for whatever requirement you're given - no matter
how silly it may seem. Populating the table will come out of how data is
being stored.


Thanks anyhow.

-Yoo-Jin

"Ed prochak" <ed.prochak (AT) magicinterface (DOT) com> wrote

"Yoo-Jin Lee" <yoojin (AT) aps-cambridge (DOT) co.uk> wrote

Ed is correct. This is a "requirement" that bears some questioning. If you
went to an architect and said "I need a house with the ability to have an
unlimited number of different rooms at the flick of a switch." What do you
think the architect would say. (after he picked himself off the ground and
stopped laughing)

Sure it is possible to do what you suggest and you will end up with a
totally unmaintainable system, difficult or almost impossible to generate
queries for, slow, unscalable etc. So are you saying the unlimited
"requirement" is more important than those other requirements. (eg
maintainability, performance, scalability) Just because it is a
"requirement" doesn't mean it is reasonably implemental or that it should be
done. I would dig deeper and find out what the business problem that is
trying to solve is. Not the business solution, the business problem. (the
unlimited columns is a business solution, not a business problem) Post the
business problem and I bet you will get some excellent suggestions on how to
solve the business problem.

Jim

Ok,

The business problem is that we have a search engine based on an item. Each
item has keys associated with it. This engine needs to be configurable for
each different organisation that uses it. The problem is that each
organisation wants to attach a different number of keys to an item.

ie.
Company A - wants to add 20 string keys, 3 number keys, and 10 date keys to
each item.

Company B - wants to add 13 string keys, 2 number keys, and 4 date keys to
each item.

When I said unlimited of course no company is going to add 1 million keys to
an item it's just has that potential.

-Yoo-Jin

"Jim Kennedy" <kennedy-down_with_spammers (AT) no_spam (DOT) comcast.net> wrote in
message news:5sQVa.19230$Ho3.3872 (AT) sccrnsc03 (DOT) ..

"Yoo-Jin Lee" <yoojin (AT) aps-cambridge (DOT) co.uk> wrote

There are a variety of search engines out there. Consider buying one.
Jim

create table Zitem(itemID int, description char(128), cost decimal(10,2),
primary key (itemID));

insert into Zitem values (1, 'this is item 1', 2.34);
insert into Zitem values (2, 'this is item 2', 7.63);
insert into Zitem values (3, 'this is item 3', 102.34);
insert into Zitem values (4, 'this is item 4', 0.65);
insert into Zitem values (5, 'this is item 5', 1.99);

select * from Zitem

create table Zitemkey(itemID int, keyID int, stringKey char(32), intKey int,
primary key (itemID, keyID))

insert into Zitemkey values (1,1,'this',null);
insert into Zitemkey values (2,1,'this',null);
insert into Zitemkey values (3,1,'this',null);
insert into Zitemkey values (4,1,'this',null);
insert into Zitemkey values (5,1,'this',null);
insert into Zitemkey values (1,2,'is',null);
insert into Zitemkey values (1,5,null,1);
insert into Zitemkey values (2,5,null,2);
insert into Zitemkey values (3,5,null,3);
insert into Zitemkey values (4,5,null,4);
insert into Zitemkey values (5,5,null,5);

select * from ZItem, ZItemkey
where stringkey='this'
and Zitem.itemid = zitemkey.itemid


The values in ZItemKey will have to be kept up-to-date, probably by using a
trigger on the ZItem table

Yoo-Jin Lee wrote:
You are describing a problem that sounds suspiciously like one
that is ideal for a data warehouse solution -- at least as far
as retrieving data is concerned. If you can either batch any
inserts and run them during the off hours or you have a
relatively low volume of inserts you should be investigating a
data warehouse solution.

Another solution alternative you should be looking at is a text
based search engine.

If you do decide on either of these approaches be aware of the
need for an incredible amount of disk space (relative to that
needed for storing the data) being required for the storage of
indexes.

In article <bg878v$6rc$1 (AT) sparta (DOT) btinternet.com>, Yoo-Jin Lee
<yoojin (AT) aps-cambridge (DOT) co.uk> writes
customers, why not try a Pick-like back end?

Adding new fields "on the fly" is no problem, and could easily be done
programmatically. Most (all?) Pick-likes support SQL queries, and will
index on fields. They are text-based databases and frugal with disk
space...

And you can download various freebies to try - there's jbase
(www.jbase.com), UniVerse and Unidata from IBM, and there's supposed to
be a freebie Pick itself (now called D3) from Raining Data.

Post on comp.databases.pick for more advice and/or help if you want to
investigate this route.

Cheers,
Wol
--
Anthony W. Youngman <pixie (AT) thewolery (DOT) demon.co.uk>
'Yings, yow graley yin! Suz ae rikt dheu,' said the blue man, taking the
thimble. 'What *is* he?' said Magrat. 'They're gnomes,' said Nanny. The man
lowered the thimble. 'Pictsies!' Carpe Jugulum, Terry Pratchett 1998
Visit the MaVerick web-site - <http://www.maverick-dbms.org> Open Source Pick