Re: Was: maximum number of columns per table

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Tue, 20 Jul 2004 06:21:05 -0700
Message-ID: <1090329689.13101@yasure>

Jay wrote:

> When you do this type of table design
>
>

>>You need to think vertically not horizontally.
>>
>>Rather than thinking:
>>
>>id, col1, col2, col3, col4, col5, col6, ...
>>  1    Y    N      Y    Y      Y     N
>>
>>think:
>>
>>id, col_number, col_value
>>1        1          Y
>>1        2          N
>>1        3          Y
>>1        4          Y
>>1        5          Y
>>1        6          N
>>
>>No matter what the data looks like ... load it relationally.
>>
>>Daniel Morgan

>
>
> How do you query back your data horizontally, without doing gigantic
> self joins:
>
> select a.col_value 'first col', b.col_value '2nd col'
> from normalized_table a, normalized_table b
> where a.id = b.id
>
> (not that, that example is gigantic but each column adds a new table
> and where clause)
>
> I find that I always end up with the above design, but then my queries
> become really ugly (especially when I want to do group bys and
> other aggregates)
>
> Thanks
> Jay

When you find a piece of paper wide enough to print 1000 columns please let me know. ;-)

After 35 years in the industry I have never found a need to use more than 50 columns except in the rarest of circumstances and even then doubt the rare exception broke 150 columns.

And not even in the days of green-bar did I ever try to print more than a few dozen columns on a single piece of paper.

If someone wants to view the details, highly unlikely, let them look at them vertically too. Those that want to see things on paper almost always want to see summaries.

Daniel Morgan Received on Tue Jul 20 2004 - 08:21:05 CDT