[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,1999,2000,2001,2002,2003)--Again the years are there to determine which years the questions where asked

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(averages, sums, percent responce, etc...), and see trends in the data over several years for any question that have stayed constant. Can anyone Help me? Is this a good design

[bdimple]

There are some Data Models for Questionnaires on this page that you might find interesting :-
http://www.databaseanswers.com/data_models/index.htm

B.Dimple

[gannet]

I would consider the following structure as a starting point.

Company(comp_id, company, sector, ...)
Question(question_id, question, ...)
Survey(survey_id, year, comp_id, ...)
Survey_Questions(survey_id, question_num, question_id)

[John Chen]

You need six tables. They are listed below :

Company (CompanyID, ...)

Survey (SurveyID, Year, ...)

Question (QuestionID, Question, ... )

Survey_Question (SurveyID, QuestionID, ...)

Company_Response (CompanyID, SurveyID, QuestionID, Response, ...)

Company_Survey (CompanyID, SurveyID, ...)


Does anybody have better ideas?

(The data model is attached)