Re: Help: creating view with different columns?

weberj@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@meta4.nl MetaKit - C++ library for persistent data