Re: More than 254 cols in table?

From: Pierre Hollard <phollard_at_bambam.turner.com>
Date: 1 Mar 1995 18:03:33 GMT
Message-ID: <3j2ctl$ler_at_tbsnames.turner.com>


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

.>
.> I'm curious if there are other business processes which also deal
.> with very wide entities and run into this limit, or if we are alone
.> in our suffering.
.>

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
------------------------------------------------------------------------------
Received on Wed Mar 01 1995 - 19:03:33 CET

Original text of this message