A Beginner Question on database design

Question

Hi All,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

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

Answer 2

Survey.Customer sounds like a storage function, while Survey.Business sounds like a retrieval function.

The only tables you need are for storage. The retrieval operations will take place using queries and reports of the existing storage tables, so you don't need additional tables for those.

Answer 3

Use a single table only. If you were to use two tables, then anytime you make a change you would in effect have to do everything twice. That's a big pain for maintenance for you and anyone else who comes in to do it in the future.

Answer 4

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 5

most of the advice/answers so far are applicable but make certain (unstated!) assumptions about your domain

try to make a logical model of the entities and attributes that are required to capture the requirements, examine the relationships, consider how the data will be used on both sides of the process, and then design the tables. Talk to the users, talk to the people that will be running the reports, talk to whoever is designing the user interface (screens and reports) to get the complete picture.

pay close attention the the reporting requirements, as they often imply additional attributes and entities not extant in the data-entry schema

Answer 6

Is the data you're presenting as the questions and answers going to be dynamic? Is this a long-term project that's going to have questions swapped in and out? If so, you'll probably want to have the questions and answers in your database as well.

The way I'd do it would be to define your entities and figure out how to design your tables so relationships are straightforward. Sounds to me like you have three entities:

• Question
• Answer
• Completed Survey

Answer 7

Just a sample elaboration of what Steven and Chris has mentioned above.

There are gonna be multiple tables, if there are gonna be multiple surveys, and each survey has a different set of questions, and if same user can take multiple surveys.

1) Customer Table with CustID as the primary key

2) Questions Table with a Question ID as the primary key. If a question cannot belong to more than one survey (a N:1 relationship), then can also have Survey ID (of table Survey table mentioned in point 3) as one of the values in the table.

3) But if a Survey to Question relationship is N:M, then (SurveryID, QuestionID) would become a composite key for the SurveyTable, else it would just have the SurveyID with the high level details of the survey like description.

4) UserSurvey table which would contain (USerID, SurveryID, QuestionID, AnswerGiven)

[Note: if same user can take the same survey again and again, either the old survey has to be updated or the repeat attempts have to stored as another rows with some serial number)

Answer 8

i think 2 tables needed:

• a survey table for storing questions and choices for answer. each survey will be stored in one row with a unique survey id
• other table is for storing answers. i think its better to store each customers answer in one row with a survey id and a customer id if necessary.

then you can compute results and store them in a surveyResults view.

Answer 9

I think u need a few tables separately for customers, questions, answers, transactions. A Purchaser has access to all the questions. But the Question is a specific subset of responses. U need properly make us a unique key for each table. Sorry for my English:) may be try php tutorials to understand