Re: More than 254 cols in table?

phollard_at_bambam.turner.com (Pierre Hollard) wrote:
>From: phollard_at_bambam.turner.com (Pierre Hollard)
>Newsgroups: comp.databases.oracle
>Subject: Re: More than 254 cols in table?
>Date: 1 Mar 1995 18:03:33 GMT
>Organization: Turner Broadcasting Company
>
>..In article <D4qJHA.FKM_at_freenet.carleton.ca>, 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

.. text deleted ...

>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.
>
>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 (there is never a need to add, changed, or modify any column!). This should also greatly improve the performance and efficiency of your application (even though the main table will have 800 times as many rows).
>
>
>------------------------------------------------------------------------------
>Pierre Hollard
>Oracle Database Administrator Phone: (404) 827-0656
>Turner Broadcasting System, Inc. E-Mail: phollard_at_dev1.turner.com
>
> ... blah blah blah ...

Mr. Hollard and other World Netizens,

With your design, what is the best (quick?) way to get a report in the following format:

HouseHold q1 q2 q3 q4 ... q800
--------- -- -- -- -- ... ----
        1  Y  N  N  Y ...    Y
        2  -  N  Y  Y ...    N
        3  N  N  N  N ...    N

I'm no rocket scientist, so I'll defer to normalization orthodoxy, but this design/report issue has vexed me before. What am I doing wrong if I can't figure out how to easily get a simple "natural" report format out of this kind of normalized schema?

I'll summarize any private email.

Thanks for helping enlighten those of us with low brain power,

Eric D. Pierce
Data Janitor
CSUS (ps, working at TBS must be fun!) Received on Wed Mar 01 1995 - 23:04:43 CET