Re: More than 254 cols in table?

ah513_at_FreeNet.Carleton.CA (Doug Harris) writes:

>In a previous posting, Steve Edelstein (74160.645_at_CompuServe.COM) writes:
>> I think 254 is the limit on columns (or it used to be?). But the
>> question really is, WHY would anyone want a table with even that
>> many cols, much less more that 254? This isn;t relational design.
>> A table of that size should probably be normalized severely!!!
 

> One case...Statistical Microdata. Any major survey is going
>to have to deal with more than 254 question responses. We go through
>a lot of pain spreading our data across multiple tables because of
>this limitation. A single filled out questionnaire is still one
>instance of an entity (say a household), possibly with upwards of
>800 attributes (Everything from number of residents to number of
>telephones say). There is no logical reason why this data should be
>broken down into smaller pieces, but the RDBMS forces us to
>do so in order to store it.

No logical reason? What if you add or change questions? With one table, you have to resort to DDL. Possibly invalidating some of your backups.
With a logical (normalized) setup, as below, you simply do some DML.

(I realize you've been forced to break it down, but..) Try:

	Survey(Survey_ID, Name, Other_Data...)
	Questions(Survey_ID, Question_ID, Query)
	Response(Respondant_ID, Survey_ID, Question_ID, Answer);

Also, try querying the number of 'Yes' responses you got.

select sum(decode(answer1,'Yes',1,0)+ ... +decode(answer800,'Yes',1,0)) from response_data.

as opposed to

select count(*)
from response
where answer = 'Yes' Received on Wed Mar 01 1995 - 19:19:14 CET