Hello,

I am trying to make some health-related survey for my personal research project. I am using oracle database, ASP and other web related tools. I appreciate if you can suggest me

I have this database-driven Health survey. In short, this survey starts with 2-3 Questions, based on how they answer I will display the next set of questions just like tree-branching logic. The next set of questions will depend on how they answer previous questions. I can not make database call everytime, when the application starts i want to load all this 50-100 questions in some temporary data structure and use it through out the application, once they are done, i will insert the responses in the database. Please suggest me any ideas / points that will help in this design

Health Assessment Database Schema Design


HRA_SUSER Table
User_ID Integer (Primary Key) NUMBER(30)
Subscription_ID String (Foreign Key) VARCHAR2(100)
Member_ID Integer
Member_Desc String


HRA_Survey Table
Survey_ID Integer (Primary Key) NUMBER(38) PK

Date_Entered Date
Survey_Num Integer NUMBER(8)
Survey_Desc String VARCHAR2(100). The description is a longer text that contains, for instance, instructions on how to fill the survey and is presented to the user taking the survey.
User_ID String (Foreign Key) NUMBER(30)

HRA_Questionnaire Table
Questionnaire_ID Integer (Primary Key)
Questionnaire_Desc String

HRA_Question Table
Questionnaire_ID Integer (Primary Key)
QuestionNum Integer
(Primary Key)
Type_Code Foreign Key
QuestionCategory String Demographic, Behavioral, Quality of life, Medical/Disability History, Family History, Preventive measures, Utilization measures, Readiness, Trend and Lifestyle
Survey_ID Integer

HRA_QuestionTypes Table
Type_Code Integer (Primary Key)
Type_Desc String ( Multiple-Choice(1) , Free-Text(2), Yes_No(3) )

HRA_MultipleChoiceQ_Table
Questionnaire_ID Integer
(Primary Key) Foreign Key
QuestionNum Integer
(Primary Key) Foreign Key
Choice_Number Integer
(Primary Key) Foreign Key
Choice_Desc

HRA_MCResponse Table
Questionnaire_ID Integer
(Primary Key) Foreign Key
QuestionNum Integer
(Primary Key) Foreign Key
Response_ID Integer
(Primary Key)
Choice_Number Integer
(Foreign Key)
Response_Value String

HRA_FreeTextResponse Table
Questionnaire_ID Integer
(Primary Key) Foreign Key
QuestionNum Integer
(Primary Key) Foreign Key
Response_ID Integer
(Primary Key)
Response_Text String

HRA_Feedback Table
FeedbackID Integer (Primary Key)
QuestionNum Integer Foreign Key
Feedback_Description String
ResponseID Integer Foreign Key

HRA_Consumer_Profile
Subscription_ID String (Primary Key)
Survey_ID Integer (Primary Key)
Date_Stamp Date

Any information in this issue is deeply appreciated.