Re: More than 254 cols in table?

In a previous posting, Pierre Hollard (phollard_at_bambam.turner.com) writes:

>
> I can't think of any good reason why a table should EVER need more than
> 254 columns! The example given is not a good one. What you need is not
> a table with 800 columns (!!!) but a table with only 3 columns.
> The table definition should look something like that:
>
> Table QUESTIONNAIRE_ANSWERS: HOUSEHOLD_ID NUMBER
> QUESTION_ID NUMBER
> ANSWER VARCHAR2
>
> You'll also need an HOUSEHOLDS table and a QUESTIONS table to store
> information about households and questions and to reference the
> household_id and question_id fields.

   Tried that once long ago. It made some things (adhoc queries) quite easy, other things (data entry) next to impossible. Performance killed it. You have to index every column both individually and compositely (full table scans are impractical when the table goes to more than 4 or 5 gig. Also consider that we may also need to store info about other entities and set up foreign key relationships between them. Your solution is theoretically elegant, but in most cases impractical (although we DO have one multidimensional survey system which uses this "datapoint" approach).

> In that way your main table will grow vertically instead of horizontally.
> This will simplify your application substantially in case some questions
> in your questionnaire needs to be added, deleted, or changed.

   This situation doesn't happen often.

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