Re: More than 254 cols in table?

ah513_at_FreeNet.Carleton.CA (Doug Harris) writes:

>> This is a very good thread.

Definitely, I've been looking for it for a while... I have run into the same problem trying to come up with a database design for a questionnaire type form which has approximately 1000 fields, most of which are numerical, a few are yes/no and a few are text. We are currently condisering a mix of a vertical and horizontal design. The form can be broken up into approximately 50 sections, most of which are optional. We are planning on storing each section as a separate row in a table that has approximately 90 generic columns (the maximum number of fields in a section). The table would look something like:

  SECTION_ID VALUE1 VALUE2 VALUE3... VALUE90 We are planning to store the numeric data in the above table and the text data in a separate table.

We had examined both a fully horizontal and fully vertical approach and thought that some of the problems with each were as follows:

  VERTICAL: Each field on the form would require the extra storage of

             key fields which, at the least, would include the FIELD_ID
             key.  At up to 1000 fields per form, this could be a lot of
             overhead.

             Performance was also a concern.  To retrieve all of the data
             for a form could require up to 1000 rows to be retrieved.

HORIZONTAL:  1000 columns would require at least 4 or 5 tables to be 
             joined to read all of the data.

             I think we also had some concern about the impact changes
             to the form would have on the database design.

Our current approach, that we have dubbed "Hybrid", certainly has its own problems, including that the column names do not indicate the data contained in the column (it depends on which section the row belongs to). Therefore, when reading data, our software will have to know, depending on the section, which columns to read. At the time, for the application we are developing, we feel the relative gains in performance and flexibility are sufficient to warrant the "Hybrid" approach.

We also have a requirement to make the data available to users for ad hoc reporting/querying. We will probably copy the data into another format for this purpose. We have not decided on the format for this database. The problems with the horizontal and vertical designs, as mentioned before in this thread, would include the following:

  VERTICAL: To display several fields on several forms (e.g. fields

             10 through 15 on all forms received in January), the
             query would have to include a GROUP BY and the awkward
             DECODES which the user may not be familiar with.

HORIZONTAL:  The user would have to know which fields are in which
             table and join the tables when necessary.  Also, such
             queries as, "Display all field names which do not
             contain data" for a particular form would not be very
             straightforward.

If other people have had experience with this type of problem, please add you 2 cents (or more) to this thread. As someone said before:              

>> I look forward to reading the articles to come.

--
Brian Sachar	email: sacher_at_access1.digex.net