I wrote an engine that reads a table, pumps out a survey/questionaire and builds the entire survey from the database in .Net . I utilized the placeholder control, and had it not only create a control set, but also allow for returning and editing the responses to the first set of questions. The application is totally data driven, without any requirements for hard coding of controls or data. The survey, the questions, the responses allowed - all reside in the SQL database.

Now - When I went to save the data from the control set - I seperated the values with a pipe, and saved them to a column in the database. I figured I could write all the possible queries to a result application that would satisfy 99% of the users of the application.

My Boss decided to take the engine to a whole new level. He wants the engine to be utilized by all the 32 bit apps we have currently - the problem being - since the application is data driven without any reliance on controls - how can a dynamic application satisfy a win 32 app that relys on knowing where the data is stored? With the method I have of saving the results based on the dynamic controls set and seperating the field with pipes, it isn't easy to access the specific control, and the specific answer.

I was told to stop doing the application that way and to code it so each possible answer got its own line in the database. I did so - but a simple 19 question survey that spawns 87 possible answers per question, multiplied by the amount of users the survey was sent to - well, suffice it to say - my DB for the answers done the new way has over 40,000 rows. The corporate survey I did 5 months ago, utilizing this method would of caused over 3,000,000 rows - and that ain't good.

So - In essence - I need a data storage solution that will allow me to not assign a control with a field, but rather allow for a dynamic control set to save to a db - yet still allow for easy reporting via t-sql - and it can't suck up space with a loud whoosing sound either.

would you happen to have a diagram I could take a quick look at? I think I know what you are trying to accomplish, but I would like to take a look at the exisiting scheme.

Even if you had all possible combinations stored like that, is the table very wide? Or is it just a long skinny table? And is this table full of only keys that are linked to tables that have the actual values (Like dimensions)?

Scooter McFly

I had a relevation that changed the schema I was going to use - I'm going to have a table set that creates the db questions, a table set that creates the db answers, then using the primary keys off of both those tables I'll allow for a table that holds the answers that relied on both of the other keys.

I just need to convince my boss to allow me to grow the answer table to x amount of columns as a business rule.
If I can get him to do that - I can turn this monster back into a tight application.

I'll post the what we have now if I get shot down on monday with my plans.
Thanx for your help scootermcfly.

Instead of growing the table to x amount of columns, could you add a field to that table with the answers that is a int or tinyint? Then, that would be the number of valied answers for the question and you could loop through it that way and it would grow lengthwise instead of widder. Put an index on the PK with that column and it should be pretty quick.

Let me know how you make out,
Scooter McFly

Good call on that. My primary problem is that the boss wants to be able to recreate the The application at the state it was saved, which means a corrected typo or a changed answer set would result in an essentially new survey or app. I plan on using the primary keys from both tables along with a control specific value state to save a tinyint - the only caveat will be a textarea field that I'll have to set to varchar in sql - but the table, while long will be lightning quick.

I've almost got this worked out and will be presenting the idea to the powers that be tomorrow.

There will be a lot of replication of data in the question tables and answer tables, but no replication in the user response table which is what all the reporting will be done off of.

One more question, if there are answers that would be the same for multiple questions, could you use some type of heirarchy inside one table? Such as using a Parent key for each answer, and if the answer can be used by more than one question, you could build a comma seperated list of parents.

Scooter McFly

I can't do comma seperated lists. (BTW I use pipes instead of commas)

My boss in a win32 nut that needs to be able to use simple tsql queries to retrieve data. I tried to explain to him that I could write an engine to search and parse the DB, but he's adamant on the desire for "one line - one control - one state"

Me? I was perfectly happy parking the entire kit and kaboodle into a pipe deliminated field. In my reporting function I parsed it and created a recordset - badda bing - badda boom - simple and easy to program.

But then again - I ain't the boss.