Re: Help: creating view with different columns?

J. Duhl wrote:
>
> Juergen Weber wrote:
> >
> > 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.

A given article mostly will use several columns.

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.

The customer is a hardware retailer.
A given article might use several columns, e.g. a hammer handle might use 'length','material','weight' wheras a nail might only use 'length'

> > 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 have a couple of questions and a suggestion:
> 1. What kind of data is put into the columns?

Integers and strings, data describing hardware articles for carpenters.
> 2. Does a relational database have to be used?
The data should be used for database publishing. The program used to put the data into xpress (in-between) wants ";" delimeted tables. So every system for database storage is possible, that finally creates such a table (for db publishing only a part of the articles is necessary at a time).

> 3. Can you express the problem generically without
> expressing that you have to use a tabular database?
see above

> However, since I don't fully understand your problem, this answer
> may be way off the mark, a risk I'm willing to take to get a clearer
> sense of your problem.

I am very grateful for your remarks,

Greetings,

-- 
Juergen Weber
Computer Scientist in the Prepress Business
email: weberj_at_schwend.de www: http://www.schwend.de