Re: Help: creating view with different columns?

Juergen Weber wrote:
>
> 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.
>
This is a classical design problem with relational databases. The fact that the customer wants to half-design it for you is a much more difficult
problem and not one I can solve for you ;-)

There are typically three options:

a) one table with lots of NULL values
b) one table with one row per attribute value
c) one table for each subtype

In an object-relational database such as Illustra or ODB-II you would naturally
model it as one "super-table" with lots of "sub-tables", so the data would be physically
stored as one table for each sub-type, but you could view it as one table for the super-type.
I think, from the form of your question about views, this is the solution you are looking towards.
Other people have suggested (b), and I have often had to resort to such designs myself, but
it does make query very awkward and very inefficient.

Mike Kay
M.H.Kay_at_iclnet.co.uk Received on Mon Apr 14 1997 - 00:00:00 CEST