I posted this in both the Visual Basic and MS Sql Server forums.
I'm working with: VB6 and SqlServer 2000
I'm creating an application that requires users to answer several questions and based on answers to those questions, certain conditions result, such as the ability to view certain sections of the questionaire and/or more questions appear and/or some questions become irrelavent and/or mathematical formulas work one way or another based on an answer to a question.
Here is an example:
The user has to answer a question. For this particular question, if the user chooses Yes then I must "Activate" all of the questions that are flagged with the code X. If the user chooses No then I must "Activate" all of the questions that are flagged with Y and "Deactivate" all of the questions that are flagged with Z.
I'm coming across a design issue (both with the application itself and the database). What is the best way to store all of this in a database? Should I store the questions along with the list of possible answers and their corresponding conditions in the database as well as the actual data for each record (each questionaire)? Should I be referencing the database to populate all of my questions on my VB form or should I manually put labels and combo boxes containing the questions and possible answers on my form? Or should I be using another technology to store my questions and conditions OR my results, such as XML?
I looking for suggestions. I want to optimize my program as much as possible. I don't want to slow it down by making several calls to the database, but I want to make sure I'm also taking away optimization by hardcoding everything.
First things first, you need to hammer out the exact logic behind enabling/disabling sets of controls. For instance, what happens if a control is "flagged" with both Y and Z in your example? Does it get enabled or disabled? Assuming that assigning attributes to your controls is feasable for your needs and performance is a concern, you may consider declaring control arrays for each attribute. Ie: arrY() contains a full list of all controls that have attribute Y, arrZ() contains a full list of controls having attribute Z. Again using your example, when a user chooses "No", you would then iterate through arrY() to enable all of the corresponding controls, then iterate through arrZ() to disable all the controls you wish to deactivate.
If the attributes for each control are going to change dynamically, then you are relegated to storing the information in a database. Dynamic content has to come from a dynamic source...
All the documentation that I've obtained is pretty much static (ie. if a user selects a particular answer then it's corresponding condition will result in ("THIS"). However, since this application is planning on being expanded in the future, my thinking would suggest I put everything in a database (questions, possible answers, results, controls, everything)...
Do you have any advise on constructing tables that contain form data (such as controls, form names, labels, combo boxes, etc)? I know I could query the database on what exactly I needed to put on each form, but confused on how to actually arrange them on the form...any suggestions or do you think that should be another question for the VB section of this forum?