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.

My guess is that "Customer side" means what the your business (bus survice provider) wants to ask of their customers and how those questions will be presented (web pages served to customers). The "Business side" means how your business may want store/access the data collected from the responses provided by the customers (database and query interface). Better dig up better requirements before designing anything!
–
NealBJan 29 '10 at 17:21

8 Answers
8

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.

This looks like the most logical way to do it. Determine types by some kind of flag, rather than hard-coded isolation. That way you'll be able to use the same code for each type in most situations.
–
CielJan 29 '10 at 17:23

+1 For database normalization. It might be a little complex for a newbie to database design. On the other hand, it's good to get them started with proper techniques early.
–
Elizabeth BuckwalterJan 29 '10 at 18:05

You also need a respondent table that stores the demographic information (BUT not the name, never the name is designing surveys) about each person who answers the survey. YOu need to have this not only to make sure your sample is correct from a statistical view but reporting on the results, you will want to see how differnt groups answered. Forinstance, men might like your company or product but women feel patronized when shopping there (see 99.9% of all auto dealerships for example). Thinking about what demographic info to collect is important as well.
–
HLGEMJan 29 '10 at 18:07

@HLGEM: Don't forget to add medical history. People without a spleen are not willing to wait more than 10 minutes for a bus under any circumstances. I think you can get to the YAGNI point in this exercise pretty rapidly!
–
Chris FarmerJan 29 '10 at 18:22

Agreed, otherwise you'd just be adding the same data to both tables.
–
PoindexterJan 29 '10 at 17:13

1

this makes a lot of assumptions about the structure of the questions and answers...
–
Steven A. LoweJan 29 '10 at 17:20

I totally disagree with this answer. I think the idea of storing info about the surveys, all the questions, and all the answers in the same table is silly, unless this survey is far simpler than most common surveys on the web.
–
Chris FarmerJan 29 '10 at 17:35

Chris, read carefully the question from Robert, he's not asking what structure to use, he asks only if he has to create multiple tables for the same purpose, its obvious that the better way for doing it is with at least 5 tables, Customers, Surveys, Questions, Surveys_Quextions, Customers_Questions. Take care before giving a negative vote -.-
–
TufoJan 29 '10 at 17:41

This is incorrect with proper database normalization. I think about down voting only because this is a student and should be taught proper techniques. And yes, this is the same comment I made to @gmcalab, but I find the 1 table answer offensive. Maybe not offensive, as I have not decided to down vote yet.
–
Elizabeth BuckwalterJan 29 '10 at 18:10

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.

This is incorrect with proper database normalization. I think about down voting only because this is a student and should be taught proper techniques.
–
Elizabeth BuckwalterJan 29 '10 at 18:07

@Elizabeth Buckwalter - The question I am answering is, should the data essentially be duplicated for business side and customer side. Making two copies of the SAME data. The question had nothing to do with normalization. I was merely pointing out to NOT duplicate data. So it's NOT incorrect.
–
GabeJan 29 '10 at 20:33

This is beating a dead horse somewhat, but Robert was not asking if the same data should be duplicated. He listed his vague application requirements and suggested his first two ideas which were the one- or two-table options. Then he asks "how can the database store the info?" I think his question is good, even if confusingly worded. It sounds to me like he's asking for help on best-practices advice on his schema design. Your answer is that he shouldn't do one of his suggested options, but a better answer would be that he should do neither of those options. I think your answer is confusing.
–
Chris FarmerJan 30 '10 at 3:10

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

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:

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.

Customer Table with CustID as the primary key

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.

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.