Thread: How to turn flat file to SQL

Hi,

I'm new to SQL but we want to turn some of our flat files into
SQL databases.  The flat file is the result of a survey (think of
a test) where there are some multiple choice questions, and
a bunch of "T/F" questions.  The T/F questions are just checked
boxes, which we can think of as keywords that a person chooses.

The question is, can we put this into a SQL database just as a
flat file, ie as one table, with one column per question, or should
we break it into several tables, for instance with a table of
the "keywords" (the boxes that can be checked).  Is there any
problem on a typical Linux box with running PostgreSql database
with one table of 120 columns?

Thanks,

J.

Jay,

> The question is, can we put this into a SQL database just as a
> flat file, ie as one table, with one column per question, or should
> we break it into several tables, for instance with a table of
> the "keywords" (the boxes that can be checked).  Is there any
> problem on a typical Linux box with running PostgreSql database
> with one table of 120 columns?

Well, administration with that kind of a structure will be rather a pain.  To
answer your question in 2 parts:

1) Yes, you can load the flat file with unchanged structure.
2) Yes, you will soon wish to use something better, such as:

(incomplete pseudo-SQL)

table survey_responses (
    user_id
    response_date
    ip_address
    status
    key (user_id, response_date)
)

table survey_questions (
    question_id
    question_type
    question_text
)

table survey_answers (
    user_id
    response_date
    question_id
    response
    key (user_id, response_date, question_id)
)

This will make *much* more efficient use of DB resources, but requires you to
understand relational database design.

Incidentally, among my (many) unfinished projects is to publish the structure
and code backing the OpenOffice.org User Survey as an OSS survey-generation
engine.  Someday ...

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco