locked
DB Design for dynamic data objects RRS feed

  • Question

  • Hi

    I have an interesting design issue.

    I want to create a database design were a user defined information can be created, linked and retrieved.

    I am planning to design a CMS (content management system) like framework which will provide user a capability to define his own objects store it,link it and publish it. He should be able to retrieve the values based on the fields defined in his objects.

    I am planning to have XML to pass the information between client and the server, but I am struggling with coming up with an acceptable database design for this.

    Any ideas or guidance will be highly appreciated.

    Thanks
    <!-- / message -->
    Wednesday, February 3, 2010 4:28 AM

Answers

  • Hi,

     

    I can give you an idea to make it possible.

     

    Please check the following points.

     

    1. Create a Integrated Database
    2. Create a Setting Table
      (Setting ID INT, Setting Name NVARCHAR (200), Setting Connection XML, Setting Value XML)
    3. Setting ID will become the IDENTITY Column
    4. In Settings Name you can specify the name of settings
    5. In Setting Connection keep Source DB Con XML and Destination DB Con XML
    6. In Setting Value Specify the Source DB Objects and Destination DB Objects Mappings
    7. Create Stored Procedure as Pass parameter link Setting Name and get Setting server name and Objects name from Setting Connection and Setting Value.
    8. Make Setting Connection  and value as XML Type column
    9. Create Primary XML index on these column to make it faster
    10. Create Linked Servers to point the Setting Connection Servers
    11. Please specify the schema type when creating XML for Setting Value.

     

    In one of our project to retrieve data from Salesforces.Com we have used XML Technology configurations like above to make sync the Local DB with sales force DB Data.

     

    2ND Way

    1. Create a Temp table with Maximum Column you want.
    2. Then rename the columns as per the XML data and Drop the columns you not required
    3. Then Populate the data from XML
    4. Process the data.
    5. Finally Drop the Temp table

     

    Please let us know if you need more clarification on this.

     

    Thanks,

    Sandeep

    Wednesday, February 3, 2010 7:25 AM