Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2005
    Posts
    38

    Database and Table layout

    What I have:
    3 surveys
    Australia: 170 questions 125 completed surveys
    Canada: 158 questions 150 completed surveys
    USA: 183 questions 95 completed surveys

    All answers are a rating 1 - 5 with the execption of 4 which includes (6)Other with a text field for the respondent to Specify

    75% of the questions appear in all 3 surveys the other 25% are unique to the country.

    I can simply lay it out as 3 "spread sheets" where there is 1 question for 1 field, however I have been told it would be better using a relational table layout.

    I have many ideas of how to lay it out, but I think this is best that I can come up with, any opinions or better ideas would be greatly appreciated

    Idea #1
    CREATE TABLE respondents(r_RowID PRIMARY INTEGER KEY, id VARCHAR)
    CREATE TABLE questions(q_RowID PRIMARY INTEGER KEY, question_number VARCHAR)
    where the question number also identifies the survey i.e. 'au_001' or 'ca_001'
    CREATE TABLE answers(a_RowID PRIMARY INTEGER KEY, answer INTEGER, other VARCHAR)
    which would have 1 - 5 as data for the first 5 entries then 6, 6, 'Grass' - 7, 6, 'Weeds' and so on for all those that answered (6)Other and specified
    CREATE TABLE relationship(r_RowID INTEGER, q_RowID INTEGER, a_RowID INTEGER)

    Idea #1a (meld the questions, and split out the countries)
    CREATE TABLE country(c_RowID PRIMARY INTEGER KEY, name VARCHAR)
    and now the questions that are repeated in all 3 surveys can be added only once and the ones that are unique to a country can be added at the end
    CREATE TABLE questions (q_RowID PRIMARY INTEGER KEY, question_number INTEGER)
    then the relationship table would be
    CREATE TABLE relationship(r_RowID INTEGER, Q_RowID INTEGER, a_RowID INTEGER, c_RowID INTEGER)
    ---------------------
    I will need to do a lot of statistical analasys on all the data, individual countries, across countries and things like - find the average response for question #100 from people from Australia and USA who answered '4' to question #3 and '2' to question #57, but didn't answer '1' to question #65

    Thank you for your input.
    Last edited by Jym; 09-02-07 at 12:26.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    This is a "first cut", but I think it should get you started.
    Code:
    TableName	PrimaryKey	Additional Columns
    ActualAnswers	ActualAnswerID	RespondantID (FK), QuestionID (FK), AnswerValue, description
    AnswerType	AnswerTypeID	name (AK01)
    Countries	CountryID	name (AK01)
    Languages	LanguageID	name (AK01)
    Locales		LocaleID	CountryID (FK) (AK01), LanguageID (FK) (AK01), name
    ActualAnswers	ActualAnswerID	RespondantID (FK) (AK01), QuestionID (FK) (AK01), AnswerValue, description
    Questions	QuestionID	AnswerTypeID (FK), LocaleID (FK), QuestionText
    ValidAnswers	ValidAnswerID	QuestionID (FK), AnswerValue
    -PatP

  3. #3
    Join Date
    Jun 2005
    Posts
    38
    Thank you again Pat, great help as always.

Posting Permissions

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