Help: creating view with different columns?

From: Juergen Weber <weberj_at_t-online.de>
Date: 1997/04/09
Message-ID: <5iff2f$igo$1_at_news01.btx.dtag.de>#1/1


Maybe somebody has hints for the following DB problem?

There is a database of quite different articles. The customer wants us to put everything into one single table. All articles have the article number column in common, but mostly columns are only used for some articles. So the resulting matrix is very sparsely populated.

anr ... col_i ... col_k ... col_n

anr_j1 X
...

anr_jn X
...

anr_k1                          X

...
anr_kn X

Together there are over 300 columns.
Your solution would work but I would have to specify a query with almost 300 NULL= fields.

Also it seems there is no dbms
that supports so many columns in one table. Furthermore it seems that empty table fields take space, too, so it is a great waste of storage.

So the idea is to create several tables each containing data of similar articles.
These tables should be put together by a view to simulate a table that contains everything. The tables should be *updateable* via the view.
Ideally I´d like to have a view like

CREATE VIEW V AS
SELECT * FROM T1

UNION
SELECT * FROM T2
UNION
...

UNION
SELECT * FROM Tn

If I change an article via the view the DBMS should know in which single table the article is because the article numbers are unique.

I think what I want is impossible, but maybe there is a solution out there?

Thanks for your hints.

-- 
Juergen Weber
Computer Scientist in the Prepress Business
email: weberj_at_schwend.de www: http://www.schwend.de
Received on Wed Apr 09 1997 - 00:00:00 CEST

Original text of this message