Planning systems with dynamic fields

Posted on 2012-09-17
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
    LVL 12

    Expert Comment

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

    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
    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..

    LVL 23

    Author Comment

    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

    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

    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

    @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

    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

    Thank you all for your much valued inputs :)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    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…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now