Wrox Programmer Forums
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old July 25th, 2006, 06:09 AM
Registered User
Join Date: Jul 2006
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Modifying table dynamically,storing dropdown value

I am working on a questionnaire application. The
application collects details about the user and stores
them in a database. The Admin can add/edit and delete
the questions as well as the options for the dropdown.
While working on this application, for 2 problems i
came up with 2 solutions each. I have mentioned them below. Could you please tell me which solution would be better?

I use a Response table to store the responses of all the users and a Question table to store all the questions.

Table: Question

Q-NO Questions Active Dropdown
1 Enter your name yes no
2 Location Yes yes
. . . .
. . . .

Active column is used by the Admin to soft delete (i.e. no more active) the question.
Dropdown column indicates if the response is dropdown or descriptive.

Now, the problem is how to store the responses. I have 2 options
Option 1:

Table: Response

Q_1 | Q_2 | Q_3 | Q_4 . . .
     | | |
     | | |

Here each time the Admin adds a new question, the question will be added in the Question table and a new column corresponding to the question will be added to the Response table.

Option 2:

Table: Response

Emp_no Q_NO Response
53542 1 --------
53542 2 --------
53542 25 --------
53547 1 --------
53547 2 ---------

Here there is no addition of columns. So each time the admin adds a question it will be stored in the Questions table. But no columns will be added to the Response table. The question number and the response is stored.

Could you please tell me which option would be better for Response table considering adding columns dynamically(option1) and the retrieval efficiency(option 2)?

Problem 2:

Problem 2 is how to store the dropdown values for the different questions. Even here I came up with 2 solutions.

Option 1:
A single table for all options.

Q_no Values
1 Accept
1 Decline
1 Tentative
3 US
3 UK

Option 2:
Maintain separate table for each dropdown. I.e. if the added question is of dropdown type, then create a table for that question with a single column.

Table: Q-1

Table: Q-3

Could you tell me which of the above 2 options would be better considering the space utilization (option 2) and retrieval efficiency(option 1)?


Similar Threads
Thread Thread Starter Forum Replies Last Post
Storing hash table into a file sanjh Java Basics 3 April 10th, 2009 11:31 PM
storing serialized data in MySQL table ire MySQL 3 November 13th, 2008 06:27 AM
Dynamically adding year in dropdown abhishekkashyap27 C# 2005 1 June 9th, 2008 07:44 AM
Storing multiple values from the dropdown list lily611 SQL Server 2000 0 June 21st, 2004 06:08 AM
Storing multiple values from the dropdown list lily611 ADO.NET 0 June 21st, 2004 05:21 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.