• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 172
  • Last Modified:

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

  • 4
  • 3
  • 2
2 Solutions
Saurabh BhadauriaCommented:
similar thing I did for one of the project..

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

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.
aprestoAuthor Commented:
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 :)
Saurabh BhadauriaCommented:
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..

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

aprestoAuthor Commented:
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
Numeric_1  ... now the customizable fields
String_1  ... now a series of string fields

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!

aprestoAuthor Commented:
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 :)
@apresto, well, some of the problems you mention about mapping and knowing which field to use will exist regardless of the solution you choose as you are seeking a dynamic system.

Certainly the normalized solution mentioned by Saurv would also be an option I would recommend, but of course that does not help in your concern about mapping and trying to locate fields.   In either case that has to be done as part of a dynamic interface for reporting.

I agree with Saurv that XML gives you no added benefit and creating a table for each customer sounds like the biggest nightmare of all - but I think you already discovered that.
Saurabh BhadauriaCommented:
sorry for replying late.....:)

I have written dynamic sql in below manner.

** to Get all the fields for a customer.

Declare @select_list nvarchaR(max)
select @select_list= 'F' + t1.seq_no + ' as [' + t2.field_Description + '],'
from table2 as t2 join Table1 t1 on t2.field_id=t1.field_id
where Cust_id=11
orderby t1.seq_no

set @select_list=left(@select_list ,len(@select_list)-1) --this will remove last comma

now the @select_list variable will have value like this..

'F10 as [Name of project],F30 as [Type of project] '  

now we can get all the value from table3 for a particular cust_id

exec('select ' + @select_list + ' from  table3 where Cust_id =11')


Now this  way you can write SP with input parameter cust_id. and by tweeking this sql you can easily update one or all particular value. If you want to delete a particular field then you just need to update it as null in third table.

And if you have reports in which you are useing few fields then you need to just limit the query to few fiels like

select @select_list= 'F' + t1.seq_no + ' as [' + t2.field_Description + '],'
from table2 as t2 join Table1 t1 on t2.field_id=t1.field_id
where Cust_id=11
and t1.Field_id in ('F1','F4')
orderby t1.seq_no

or if you want to achive this with multiple reports then you can take a table in which you can define report_id and the no of fields required for that report.

In report sp's you can join the new table with above query

select @select_list= 'F' + t1.seq_no + ' as [' + t2.field_Description + '],'
from table2 as t2 join Table1 t1 on t2.field_id=t1.field_id join Report_table rt on rt.field_id=t1.field_id
where Cust_id=11 and rt.report_id=1
orderby t1.seq_no

Hope this help...
aprestoAuthor Commented:
Thank you all for your much valued inputs :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now