Results 1 to 6 of 6

Thread: survey database

  1. #1
    Join Date
    Nov 2005
    Posts
    3

    survey database

    Hello. My name is David. I work for a small family business and I have been asked to develope a database that stores our survey's that we have our clients fill out on a daily basis.

    I have limited experience with databases and I was looking for opinions on my database design.

    Some Facts.
    each survey has about 30 questions.
    each survey can be filled out by a client more then once.

    Entities.
    clients and surveys

    My tables so far.

    tblclients
    ---------
    client_id autonumber/not null
    client_lanme nvarchar not null
    client_fname nvarchar not null
    client_phone nvarchar not null

    tblSurvey
    ---------
    survey_id autonumber/not null
    survery_date datetime not null
    client_id int not null
    question_1 nvarchar not null
    question_2 nvarchar not null
    question_30 nvarchar not null

    *there will be about 30 question_# fields.

    the tables are related through the client_id fields.

    Does this design look like it will work?

  2. #2
    Join Date
    Aug 2004
    Posts
    330
    You should look at putting the survey questions as a separate entitiy and the responses as a separate entity.

  3. #3
    Join Date
    Nov 2005
    Posts
    3

    Talking

    My updated tables...


    Questions
    -------------
    *question_id autonumber NOT NULL
    question_desc nvarchar(255) NOT NULL

    Survey
    -------------
    *survey_id autonumber NOT NULL
    survey_date datetime NOT NULL
    survey_client_id int NOT NULL

    Questions_Survey
    -------------
    *line_id autonumber NOT NULL
    question_id int NOT NULL
    survey_id int NOT NULL

    Clients
    -------------
    client_id autonumber NOT NULL
    client_lname nvarchar(255) NOT NULL
    client_fname nvarchar(255) NOT NULL

    Answers
    -------------
    *line_id autonumber NOT NULL
    question_id int NOT NULL
    survey_id autonumber NOT NULL
    responce1-50 nvarchar(255) NOT NULL

    I hope my tables look alright, I have many more questions, but I think its safe to say I should get my design set first before I start about inserting data.

  4. #4
    Join Date
    Aug 2004
    Posts
    330
    Do you plan for a Question to be listed on multiple surveys?

    Why do you have question_id and survey_id columns in your Answers table? Shouldn't the Answer reference a row on the Questions_Survey table?

  5. #5
    Join Date
    Nov 2005
    Posts
    3
    It will be the same questions being asked on each survey. Will this change my design? I thought my design already reflected that?

    I will adjust my tables according to your suggestion, it makes sense

  6. #6
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    This is a simple problem, keep the solution equally simple
    Attached Thumbnails Attached Thumbnails untitled.JPG  

Posting Permissions

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