Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2003
    Posts
    6

    Unanswered: Database Design for Survey data

    I am creating a database of surveys completed by companys over the last 5 years. The question have varied from year to year. Adding questions some years, and dropping some in other years. each survey is about 800 questions. I had the following tables in mind.

    Company(Company,CompanyID,Sector,1999,2000,2001,20 02,2003)---the years are to determin when each company participated

    1999Survey(Company,CompanyID, Q1, Q2,.....QN)
    .
    .
    .
    .
    2003Survey(Company, CompanyID, Q1, Q2,.....QN)

    Questions(QuestionNumber, Question description)

    Does this seem like a good way to oraganize the data. how would the relationships work. My goal is to be able to analyze the data for a given year, and see trends in the data over several years for any question that have stayed constant. Can anyone Help me?
    Last edited by shyam131; 07-03-03 at 01:29.

  2. #2
    Join Date
    Mar 2002
    Posts
    192
    here is a simplified solution

    tblCompany
    - CompanyID (PK)
    - CompanyName

    tblAnswer
    - AnswerCompanyID (PK)
    - AnswerQuestionID (PK)
    - AnswerAnswer

    tblQuestion
    - QuestionID (PK)
    - QuestionSurveyID
    - QuestionText
    - QuestionRightAnswer

    tblChoice
    - ChoiceID (PK)
    - ChoiceQuestionID
    - ChoiceSequence
    - ChoiceText

    tblSurvey
    - SurveyID (PK)
    - SurveyName
    - SurveyYear

    RELATIONSHIPS

    tblCompany --< tblAnswers >-- tblQuestions >-- tblSurvey
    |
    |
    ^
    tblChoices

  3. #3
    Join Date
    Mar 2002
    Posts
    192
    Using this design you can have as many years as you like, and as many choices for a given question as you like.

    I.E>


    Q1 4 choices
    Q2 2 choices
    Q3 8 choices

  4. #4
    Join Date
    Mar 2002
    Posts
    192
    just noticed that this forum strips off spaces..

    The choices table should be linked to questions not company as shown.

  5. #5
    Join Date
    Jul 2003
    Posts
    6
    I dont' quite understand your ideas of having quesiton choices??? The survey has a lot of dollar amounts, and percentages, and yes/no questions. And there is no correct or incorrect answer.

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    a possible setup attached

    nothing strange here except maybe QuesType which is a lookup to a typed set of values 1-Text; 2-Bool; 3-Double depending on the type of answer expected for each question.

    izy

  7. #7
    Join Date
    Jul 2003
    Location
    WaffleHouse, Ga
    Posts
    5

    Re: Database Design for Survey data

    Originally posted by shyam131
    I am creating a database of surveys completed by companys over the last 5 years. The question have varied from year to year. Adding questions some years, and dropping some in other years. each survey is about 800 questions. I had the following tables in mind.

    Company(Company,CompanyID,Sector,1999,2000,2001,20 02,2003)---the years are to determin when each company participated

    1999Survey(Company,CompanyID, Q1, Q2,.....QN)
    .
    .
    .
    .
    2003Survey(Company, CompanyID, Q1, Q2,.....QN)

    Questions(QuestionNumber, Question description)

    Does this seem like a good way to oraganize the data. how would the relationships work. My goal is to be able to analyze the data for a given year, and see trends in the data over several years for any question that have stayed constant. Can anyone Help me?

    If I understand you correctly, I would create something like tblCoSurveys with the fields you indicated, but with CompanyId as my first field, because it will be your Unique Field

    I would put my yrs under a field (i.e., Column) called Year

    I would build a main qry called qryCoSurveys, and then I would build other qry's from this main qry, e.g.: qryCoSurveys1999 -- I tend to like tbls strickly to hold my data and work from a main qry based on the particular tbl

    qryCoSurveys1999's "Year" field criteria would be "1999," and I would uncheck to show the subsequent fields (i.e., Columns) I did not want to show

    I would march through the years, hence, building qry's of what I needed

    Since qry's are really SQL statements, you can drill into tblCoSurveys via qry's WITHOUT making your db a memory hog

    -z

  8. #8
    Join Date
    Jul 2003
    Posts
    6
    izyrider thanx....

    Your solution was great and a very helpful, but the only catch is that the survey has about 854 questions. This would force me to use mulitple tables for the answers. Do you think I will encouter any problems using your design with the answers table split amoungst multilpe tables?

  9. #9
    Join Date
    Mar 2002
    Posts
    192
    OK back. The choices were there b/c you did not specify and I had to assume that it would be like most surveys out there. If you do not need multiple choice then remove the choice table. My bad on the "answer" field. Remove that as well. The rest of the design will do the job.

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    sure you can split things up into multiple tables. split by answer type (i.e. one table for booleans, one for text, one for doubles etc) would make your db smaller by eliminating the empty fields. or split by year? try a few tests and make sure you can query your data back together again for the sort of reports you intend to run. it will be messy with multiple tables.

    10 years x 50 companies is ~400,000 answer records and ~30MB: big, but do-able in one table. it wont be fast!

    how do you persuade your victims to answer a survey with 854 questions?


    izy

  11. #11
    Join Date
    Jul 2003
    Posts
    6
    See attached setup

    My victims are clients that pay to participate in the surveys. Though with 854 questions, the responce rates vary drastically. I thank god that it is only an annual survey!

    I came up with my design. I have all the answers (CCBSPT1 -- CCBSPT4) split across 4 tables, that will include data from all 7 years. I have set up the realtionships. After running a few queries I am easily able to put the data back together. Take a look let me know if you see any potential problems (attached file). I have imported data for one year (2003), and my database is 36MB, is that excessively larger? I am not really worried about speed, just getting the job done.

    My reports will mostly be averages of the answers to the surveys, max, min, percent responce, etc....
    Attached Files Attached Files

  12. #12
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    it's not pretty, but you can do it that way: your mammoth-sized table definitions with 2XX fields per table x 4 is more flat-file than relational.

    if you must keep this design, some thoughts:

    don't include field.Company in table.Participants or table.CCBSPT1, 2, 3, etc. once you have field.CompanyID you can lookup Company any time you need it.

    table.Questions looks a bit lost -- ?? no way to link field.QuestionspaceCode with the field.Q1; Q2 etc ?

    ...and do yourself a favour: eliminateAllSpacesAndHyphensFromYourFieldNames (and control names), you will save yourself hours of searching for "[" and "]" keys, and appropriate capitalisation makes things just as readable. compare: [Question Code] with QuestionCode - when you are trying to debug a long SQL statement, the bracket-word-space-word-bracket stuff drives you nuts.

    don't worry about db size/records: theoretically access can handle 2pointSomething billion records if you are not in a rush. 36MB is nothing!

    bottom line is that this is not a relational database theory exam & you are not looking for multi-user or lightning fast response - if it works & if you can maintain it: it's good enough! with one year between surveys you get lots of opportunity to make it better later.

    izy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •