Re:A database model for an online survey
by kellros on Thursday March 05, 2015 @09:16AM
(#9150840)
Sorry to say, but the design is change intolerant and will most likely fail to meet expectations. Changing the questions or answers will cause all current responses to be invalidated or lost when a question or answer is deleted. The sizes (e.g. varchar(200) for a question) aren't necessarily accurate - what about really long questions or answers?
Typically I'd expect to see:
- Survey (details + configuration)
- Questions (details + configuration; incl. the order)
- Answers (details + configuration)
Survey 1 - * Questions 1 - * Answers
A separate audit table is preferred if you plan to track who changed what and when (ref: "updated" on tables). I'd rather include a "started_at" and "completed_at" on the "survey_response", other metadata like user agent, IP, client time zone etc. would be pretty useful. There's other configuration that's also missing, e.g. from when to when is the survey available? what's the limit of responses? can someone complete the survey twice (or more)?
Overall I'd rate the current design 3/10. It might be good enough for a beginner tutorial, but for production purposes it's not. I'd suggest you go back to the planning phase and review how your design meets your (clients) goals and how realistic it is in terms of usage/ability to change/future needs that can be accommodated now (typically things such as how long it took to complete a survey, or other details - you can't get that data later on if it was never recorded when it was made available).
Re:A database model for an online survey
by MEdward on Saturday March 07, 2015 @06:05AM
(#9161575)
Kellros, I think you just didn't read carefully that this is the first part of the series which final aim is to give us a complete and correct database model. So, maybe wait with your comments and marks till the last episode and a final model, OK?
Typically I'd expect to see:
- Survey (details + configuration)
- Questions (details + configuration; incl. the order)
- Answers (details + configuration)
Survey 1 - * Questions 1 - * Answers
A separate audit table is preferred if you plan to track who changed what and when (ref: "updated" on tables). I'd rather include a "started_at" and "completed_at" on the "survey_response", other metadata like user agent, IP, client time zone etc. would be pretty useful. There's other configuration that's also missing, e.g. from when to when is the survey available? what's the limit of responses? can someone complete the survey twice (or more)?
Overall I'd rate the current design 3/10. It might be good enough for a beginner tutorial, but for production purposes it's not. I'd suggest you go back to the planning phase and review how your design meets your (clients) goals and how realistic it is in terms of usage/ability to change/future needs that can be accommodated now (typically things such as how long it took to complete a survey, or other details - you can't get that data later on if it was never recorded when it was made available).