Re: More than 254 cols in table?

In a previous posting, L. Scott Johnson (sjohnson_at_math.scarolina.edu) writes:  

> No logical reason? What if you add or change questions?

   As I posted previously, it doesn't happen. Survey designs are no less stable than database designs.

> 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.

   To all questions? Thats a fairly useless statistic. I'm more likely to want to know the average number of telephones per household for each city.

   SELECT City, AVG(Telephones)

      FROM Households   
      GROUP BY City;

  That's fairly straighforward, even for an analyst playing with an end user query tool. Now lets try the other way...

   SELECT Cities.Answer, AVG(Telephones.Answer)

      FROM Response AS Cities, Questions AS City_Question,
           Response AS Telephones, Questions AS Telephone_Question, Survey
      WHERE Cities.Question_ID = City_Question.Question_ID
        AND City_Question.Query = 'City'
        AND Telephones.Question_ID = Telephone_Question.Question_ID
        AND  Telephone_Question.Question_ID = 'Telephone'
        AND Cities.Respondant_ID = Telephones.Respondant_ID
        AND Cities.Survey_ID = Surveys.Survey_ID
        AND City_Question.Survey_ID = Survey.Survey_ID
        AND Telephones.Survey_ID = Survey.Survey_ID
        AND Telephone_Question.Survey_ID = Survey.Survey_ID
        AND Survey.Name = 'Household Survey'

  (Hmm, did the lights just dim in the server room?)

   Now the maximum tables joined per query is quite a bit smaller than the maximum columns per table so for anything much more complex (and this is a *very* simple example) I'll have to resort to DECODE, and views layered on views. Most of the people running these types of queries can't tell GROUP BY from ORDER BY and would be hard pressed to come up with anything close to accurate results.

   Now suppose I want to build an Oracle Form app for data entry. A form looking anything like:

Question Name Question Answer
------------- ---------------

ID                  100000001
City                Ottawa

   etc etc would be laughed out of the key entry room. It's got to look like

   ID: _______ City: _______ Telephones:_________

just like it appears on the questionnaire. Forget about doing this with "default block". You are going to need a separate block for each field with all blocks specifying ID as "Master". Don't forget that each question has it's own set of validation rules (Ranges, Patterns, City must exist in City Table, etc etc). I've tried it. It's a nightmare!

   The whole attraction of a relational database is that it makes it EASY to work with your data. From a practical point of view we are dealing with "Companies" and "Departments" and "Employees" just like everyone else. We just need to store much more detailed and varied attributes of those entities. Breaking the data down into datapoints is elegant at first sight but it just makes life too awkward in practice.

--
   - Doug Harris
     Database Administrator, System Development Division,
     Statistics Canada.        ## WHERE ALL_OPINIONS.OWNER = USER ##