[Last Call] Learn how to a build a cloud-first strategyRegister Now


Planning systems with dynamic fields

Posted on 2012-09-17
Medium Priority
Last Modified: 2013-01-21
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

Question by:apresto
  • 4
  • 3
  • 2
LVL 12

Expert Comment

by:Saurabh Bhadauria
ID: 38408876
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.
LVL 23

Author Comment

ID: 38409203
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 :)
LVL 12

Expert Comment

by:Saurabh Bhadauria
ID: 38409235
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..

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 23

Author Comment

ID: 38410085
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?
LVL 39

Expert Comment

ID: 38417637
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!

LVL 23

Author Comment

ID: 38423625
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 :)
LVL 39

Assisted Solution

gdemaria earned 800 total points
ID: 38423856
@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.
LVL 12

Accepted Solution

Saurabh Bhadauria earned 1200 total points
ID: 38424617
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...
LVL 23

Author Closing Comment

ID: 38802840
Thank you all for your much valued inputs :)

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
The viewer will learn how to count occurrences of each item in an array.
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question