Re: Help: creating view with different columns?

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

I have a couple of questions and a suggestion:

1. What kind of data is put into the columns?
2. Does a relational database have to be used?
3. Can you express the problem generically without

   expressing that you have to use a tabular database?

My point is this: if all you have is a hammer then every problem will look like a nail. Specifically if it must be a relational database and the data storage structures must be tables then you have to do it in a relational manner. However, if it doesn't have to be a relational database and if you can describe the problem more generically, then perhaps you can use an object database.

If, for example, the column values are simple "yes" or "no" values representing whether whatever property the column stands for exists or not, then the entire array can be represented in a bit-vector and that can be a single property of an object in an object database. You can then define specific methods on it as well to update the fields.

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.

Regards,

Joshua Duhl

Stillpoint Consulting
74443.2610_at_compuserve.com Received on Wed Apr 09 1997 - 00:00:00 CEST