

A database model for an online survey - pai1009
http://www.vertabelo.com/blog/technical-articles/a-database-model-for-an-online-survey-part-1

======
kellros
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).

~~~
MEdward
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?

~~~
jedison
kudos to that, MEdward

