Planning systems with dynamic fields
Posted on 2012-09-17
I am planning the rewrite of our main system at work. I work for a telemarketing company and every time we win a new client, we need to ceate a load of new pages and classes with different questionnaire pages to capture different information.
The main problem with this is that we need to create a seperate database for each new client.
One of the main drawbacks of the current system is that each database has a few tables with different fields.
What i would like to achieve with the rewrite is to use one single database for all clients, and have our client services department create surveys from an interface. There ae a couple of routes i was thinking of going down:
1. Use a table of key/value pairs to capture the questions and answers, this allows a geat deal of scalabiity. Problem here is the reporing element, running and writting a series of pivot queries is as time consuming as reating the surveys and possibly more stressful onthe servers.
2. Use XML some how to achieve this. Any suggestions?
I really dont want to allow users to add field to tables programatically, i have done this in the past a proved very problematic.
My man concern is the reporting, we use some cube logic (which requires a flat file) and we also use SSRS.
Can someone suggest a viable solution to this problem.
This question is the first of many on the subject.
Thanks for reading