A Beginner Question on database design

Question

this is a follow-up question on my previous one.We junior year students are doing website development for the univeristy as volunteering work.We are using PHP+MySQL technique. Now I am mainly responsible for the database development using MySQL,but I am a MySQL designer.I am now asking for some hints on writing my first table,to get my hands on it,then I could work well with other tables. The quesiton is like this,the first thing our website is going to do is to present a Survey to the user to collect their preference on when they want to use the bus service. and this is where I am going to start my database development. The User Requirement Document specifies that for the survey,there should be

Customer side:

Survery will be available to customers,with a set of predefined questions and answers and should be easy to fill out

Business side:

Survery info. will be stored,outputed and displayable for analysis.

It doesnt sound too much work,and I dont need to care about any PHP thing,but I am just confused on :should I just creat a single table called " Survery",or two tables "Survey_business" and "Survey_Customer",and how can the database store the info.? I would be grateful if you guys could give me some help so I can work along,because the first step is always the hardest and most important. Thanks.

Answer 1

I would use multiple tables. One for the surveys themselves, and another for the questions. Maybe one more for the answer options, if you want to go with multiple-choice questions. Another table for the answers with a record per question per answerer. The complexity escalates as you consider multiple types of answers (choice, fill-in-the-blank single-line, free-form multiline, etc.) and display options (radio button, dropdown list, textbox, yada yada), but for a simple multiple-choice example with a single rendering type, this would work, I think.

Something like:

-- Survey info such as title, publish dates, etc.
create table Surveys
(
    survey_id number,
    survey_title varchar2(200)
)

-- one record per question, associated with the parent survey
create table Questions  
(
    question_id number,
    survey_id number,
    question varchar2(200)
)

-- one record per multiple-choice option in a question
create table Choices
(
    choice_id number,
    question_id number,
    choice varchar2(200)
)

-- one record per question per answerer to keep track of who
-- answered each question
create table Answers
(
    answer_id number,
    answerer_id number,
    choice_id number
) 

Then use application code to:

1. Insert new surveys and questions.
2. Populate answers as people take the surveys.
3. Report on the results after the survey is in progress.

You, as the database developer, could work with the web app developer to design the queries that would both populate and retrieve the appropriate data for each task.

Answer 2

only 1 table, you'll change only the way you use the table for each ocasion

customers side insert data into the table

business side read the data and results from the same table