Re: Table storage question

Larry Molter wrote:

> My boss asked me to create a general purpose table that we can use to
> hold responses from a variety of customer interview forms. The table
> would consist of 20 char(1) fields and about 100 varchar2(255) fields.
> To add to that, I have to migrate an existing customer table to use
> the new general purpose table. The existing table has about 66K
> records.
>
> I had an alternate idea to create a table that would hold a key and
> value pair, resulting in a record for every answer on the form, not
> just one big record representing the whole questionnaire. (I hope that
> was clear). So, my big question is, what are the pros and cons of a
> large numbers of columns but small amount of data vs. a few columns
> but a large amount of data?
>
> I can be more specific if no one can figure out what I'm trying to ask
> <g>.
>
> Larry Molter
> Diabetic Supply of USA

Your boss needs to take a class called Database 100 also known as Bonehead RDBMS. What he asked you to create is called a flat file and has almost no business being in a relational database. Any table with more than a few dozen fields should be highly suspect of being indicative of a bad design (not a guarantee but certainly suspect).

What you should create is something like the following

CREATE TABLE customer (
 customer_id NUMBER(10),
 customer_name VARCHAR2(40));

CREATE TABLE form (
 form_id NUMBER(10),
 form_name VARCHAR2(40));

CREATE TABLE responses (
 customer_id NUMBER(10),
 form_id NUMBER(10),
 question_id NUMBER(10),
 response VARCHAR2(255),

Think vertically ... not horizontally. The mess he has set out to make will make reporting difficult and will require that new fields be added very time a new question is added and as question are no longer used columns become orphans.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
http://www.outreach.washington.edu/extinfo/certprog/aoa/aoa_main.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)