Planning systems with dynamic fields
Afternoon experts.
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
Apresto
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
Apresto
ASKER
Thanks for your reply Saurv
I'm familiar with this approach. However my primary concern is the reporting element. The purpose of the rewrite is to create a system that will be manageable by the users themselves, going with a solution like this will require writing a pivot of some sort to display the information on reports and SSRS. Would you agree?
How did you find querying the information when you created your project. In the past i have found it difficult with SQL alone and have had to reply heavily on .Net, which serves a problem for our DBA's :)
I'm familiar with this approach. However my primary concern is the reporting element. The purpose of the rewrite is to create a system that will be manageable by the users themselves, going with a solution like this will require writing a pivot of some sort to display the information on reports and SSRS. Would you agree?
How did you find querying the information when you created your project. In the past i have found it difficult with SQL alone and have had to reply heavily on .Net, which serves a problem for our DBA's :)
I was heavily rely on dynamic Sql for all operations..
we generated dynamic query string at run-time with in a stored procedure with the help of first two tables and execute it .
And this dynamic query is very efficient and creates no performance issues for us..
-Saurabh
we generated dynamic query string at run-time with in a stored procedure with the help of first two tables and execute it .
And this dynamic query is very efficient and creates no performance issues for us..
-Saurabh
ASKER
So did you use a series of cursors to loop these index tables to create the dynamic SQL? And did the dynamic SQL involve pivot statements?
If you have a significant set of "core" or standard fields that all users will utilize then consider this approach. Write your table with these standard fields, then add a series of "definable" fields to the same table. You could add, say, 30 extra fields for customization. This will give you a design that is more easily reported against. You could add 100 fields if you wanted, each with different datatypes.
My DataTable
-------------------
DataTable_ID Pkey
Name --- all the common fields
Address
Phone
Email
etc...
Numeric_1 ... now the customizable fields
Numeric_2
Numeric_3
...etc..
String_1 ... now a series of string fields
String_2
String_3
...etc..
date_1
date_2
In a separate table, for each customer, you keep track of the customized field that is used, the name of the field, if it's required, etc.
But you already are enforcing the datatype by having the user select the type of field, and then assigning the custom label to the currect field. So, if the user wants a new date field, and he already has 2 date fields, you assign the "Hire Date" (or whatever) to date_3.
This is a pretty easy approach and good for reporting provided you have a finite number of customizable fields, which most people do... that number can be pretty large!
Numeric_3
My DataTable
-------------------
DataTable_ID Pkey
Name --- all the common fields
Address
Phone
etc...
Numeric_1 ... now the customizable fields
Numeric_2
Numeric_3
...etc..
String_1 ... now a series of string fields
String_2
String_3
...etc..
date_1
date_2
In a separate table, for each customer, you keep track of the customized field that is used, the name of the field, if it's required, etc.
But you already are enforcing the datatype by having the user select the type of field, and then assigning the custom label to the currect field. So, if the user wants a new date field, and he already has 2 date fields, you assign the "Hire Date" (or whatever) to date_3.
This is a pretty easy approach and good for reporting provided you have a finite number of customizable fields, which most people do... that number can be pretty large!
Numeric_3
ASKER
Hi gdemaria
Thank you for your input. We actually explored (and ruled out) this opion early on.
You are right, for predefined reports it shouldnt be a problem, but I'm reluctant to use such a method purely because it's pretty inefficient, its abit scrappy and what if i have 30 varchar fields and i need 31? I know i could add another field to the able (and alter views, sproc and interface code), but when does it stop?
We implemented a telephony system that used such a structure and running simple queries or importing data proved an absolute nightmare. Predefined reports are well and good, but when you have to continually refer to mapping tables and external schema files just to work out what field holds which value it is extremely time consuming and tedious. And although it seems scalable, the amount of time you ill spend faffing around will eventually outweigh having spent abit more time coming up with a more dynamic ay of working.
I'm sure for some projects this may prove to be a suitable solution, but having already investigates, it's isn't feasible for this one
I do thank you for you input though :)
Thank you for your input. We actually explored (and ruled out) this opion early on.
You are right, for predefined reports it shouldnt be a problem, but I'm reluctant to use such a method purely because it's pretty inefficient, its abit scrappy and what if i have 30 varchar fields and i need 31? I know i could add another field to the able (and alter views, sproc and interface code), but when does it stop?
We implemented a telephony system that used such a structure and running simple queries or importing data proved an absolute nightmare. Predefined reports are well and good, but when you have to continually refer to mapping tables and external schema files just to work out what field holds which value it is extremely time consuming and tedious. And although it seems scalable, the amount of time you ill spend faffing around will eventually outweigh having spent abit more time coming up with a more dynamic ay of working.
I'm sure for some projects this may prove to be a suitable solution, but having already investigates, it's isn't feasible for this one
I do thank you for you input though :)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you all for your much valued inputs :)
we implemented this with below manner .. Handling this with XML seems bad idea you will end up parsing lot of xml.
In one table we defined the all field type
like
Field_id Type seq_no
--------------------------
F1 1 10
F2 2 20
F3 3 30
......................
Here type defines datatype like int , varchar
In another we add Field description for each customer
Cust_id Field_id Field_Description
--------------------------
11 F1 Name of project
11 F3 type of project
12 F1 Area name
12 F2 Area ID
And in another table we stores answers for all customers with field seq no's .
I have taken all the Field column as nvarchar(max)
Cust_id F10 F20 F30 ......................
11 project1 null Government ..............
12 India 25 null ........................
Now you need to write some dynamic SQL to access data for a customer.