Oracle Design Question 1

[jetec]

I have a checklist form that has 44 questions? A simple 1 or 0 is collected.

My question is, should I create a wide table with 44 fields, plus an additional two fields that identify the user and form, or should I have a "user/form" table and then a "detailed" table with a "user/form" key that relates the detailed information collected into the detail table.

For example:
USER_FORM
---------
ID
USERNAME
FORM


DETAILED
--------
FK_ID --RELATES BACK TO USER_FORM
QUESTION_ID -- identify's the question
QUESTIONS_VALUE --- 0 OR 1

Note, there will probably be thousands of these forms submitted?

Thanks for any ideas or recommendations

 

[SantaMufasa]

Jetec,

Your scenario represents the classic "battle" between complying with (E. F. Codd's) First Normal Form (by implementing the two-table solution for the sake of "design purity") versus completely disobeying First Normal Form (by implementing the 40-column, single-table solution for the perceived sake of performance.)

My feeling is that you design purely, then if performance is unacceptable, you degrade the design to determine if the degradation produces acceptable performance results.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[jetec]

Thanks Mufasa! I'll go with the two tables and generate some data to test out performance.