Was: maximum number of columns per table

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 Received on Tue Jul 20 2004 - 08:08:04 CDT