Re: Design question - best practice to store survey questions


Edwinah63 wrote:
Hi Ed,

Many thanks for your reply. Your response to the design of tblSurveys
is correct, there would be many more attributes including the ones that
you suggested but for the sake of brevity, it is simpler just to put
the bare bones down.

tblSurveyQuestions: Some questions will be common to all surveys, but
most surveys will have a number of questions specific to them. I
understand that it would be good to remove any redundancy in the
questions themselves, but again we will allow it for the present.

If you don't plan for the database to deal with this now, you will need
significant changes later, or pay for more disc space to cope with the
redundancy.


Re no design for the answers tables: Well, that is my question :) Based
on the sample data, what would be the better design.

I could go with an idea found on google groups

tblSurveyAnswers
AnswerID PK int
SurveyQID FK int
Answer text

OR

design a new table to contain the answers for each survey - Yikes!!

Re types of queries, we need to be able to add, edit and retrieve
question sets and answers. The web pages are static at present, but in
the future, we may call the question sets from database and then
dynamically build the webpage.

At the end of the day, I'm wondering if other people have had this
question and what solution/s they applied to the design.

Edwinah63

I repeat that a big part of your problem is that you are thinking of
tables and IDs (physical implementation) during the logical design
phase of your database. Consider this question:

WHAT IS AN ANSWER?

What kind of thing is it? does it exist in isolation? Does it relate to
anything?


Here's some help: go back a big step and start drawing an ERD (entity
relationship diagram). You already started thinking about three of the
main parts. (there's at least one other main part you haven't
explicitly mentioned yet. I'll mention it later if you are still
missing it.)

in your diagram, DO NOT put any ID attributes. Label the relationships
with connecting verbs. So above the line between SURVEY and QUESTION
might be labelled "HAS", for the relation: SURVEY HAS one or more
QUESTIONs. And below the line you might also write IS CONTAINED IN, for
the relation that QUESTION IS CONTAINED IN one or more SURVEYs.

I think if you do this, you will find the design you need. Ask yourself
questions about how things hold together. What KINDS of questions do
you want to handle? (e.g. do all text response questions always have
the same maximum numbers of characters/lines/whatever?) What kinds of
surveys? (e.g. are all questions always asked for a given survey? Are
they always asked in the same order? Is there an order to the
questions?) What kinds of results are expected/required? (just
recording the "answers"? Does the time the survey is taken matter? does
the time each question is responded to matter? Do you need to know who
entered the data? IOW is this a callcenter application or an open
website? How do you identify the source of the survey answers? Do you
trust a person to enter a real name, or do you have a login?) Do the
responses to these issues indicate more specialized entities? (eg. a
MULTIPLECHOICE type QUESTION versus expecting a NUMERIC response. Does
the multiple choice allow more than one response? Do you need exact
numeric responses, or will multiplechoice with ranges of values be
enough?)

I really don't want to suggest a single answer because every
application is different. And surveys in particular can have so many
options.

Let me know if I'm leading you on the right path.
Ed

.

Relevant Pages

  • RE: For Duane Hookom
    ... You should be able to just stick with the forms for entering your survey ... available from the survey design form. ... The purpose of the database is to input the data from surveys that have been ... For instance, if I required a numeric response type, then I ...
    (microsoft.public.access.tablesdbdesign)
  • Suggested filter rule for the flood of 2007-07-25 12:51 GMT (6)
    ... Try feeling the covenant's unique sediment and Ghassan will double you! ... Are you standard, I mean, securing in response to ... Roxanne smooths the survey on the part of hers and ie grips. ...
    (sci.crypt)
  • austin, still assisting, quits almost regularly, as the broker explores on behalf of their nonsense
    ... single past hers and just about melts. ... Mustapha will tomorrow protect them under you. ... throw in response to the ready company. ... survey under the compound? ...
    (sci.crypt)
  • Re: lots of aces inevitably spare the overseas expedition
    ... Tom's warehouse might design some unemployed runs. ... You won't imagine me designating in response to your burning ... striking docks. ... I was persuading hosts to territorial Shah, ...
    (sci.crypt)
  • Re: Questionnaire
    ... define your survey question. ... > What are your thoughts on my design idea and whether I should incorporate ... I am working on a questionnaire entry and analysis database which is ... > Answer Value (i.e. this is the response to the question for that ...
    (microsoft.public.access.tablesdbdesign)