Re: Help: creating view with different columns?

weberj_at_t-online.de (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.

Juergen,

Have you considered the following table structure?

	anr_jl	col_i	X
	anr_jn	col_i	X
	anr_kl	col_k	X
	anr_kn	col_k	X
	...

The combinatiion of anr_* and col_* would form the primary key.

Another way of looking at this is to turn from a two-dimensional data format (as your example shows) to a coordinate-oriented row structure.

This is a classical case of placing the variation in the woring dimension, ie. if the columns vary so widely, they should be rows instead. It is likely that when you add a new article, you will also want to add a new column in some situations.

>Also it seems there is no dbms
>that supports so many columns in one table.

No, even my own tiny "MetaKit" C++ class library will handle it.

>Furthermore it seems that empty table fields
>take space, too, so it is a great waste of storage.

No, MetaKit automatically adapts to integer sizes and string sizes.

>So the idea is to create several tables each containing data
>of similar articles.

No, now you are moving from variation in -columns- to variation in -tables-. From a relational perspective, variation should be in rows.

There are many other types of problems which need remodeling to fit a relational structure. Parts explosion / tree storage is another data structure which can be perfectly represented in the relational model, once you choose the appropriate transformation.

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

I think there is. Your example is not really such an uncommon case.

Regards,
Jean-Claude

  Jean-Claude Wippler                            http://www.meta4.com/
  Meta Four Software         CatFish - A freeware disk catalog browser
  jcw_at_meta4.nl               MetaKit - C++ library for persistent data