locked
Recursive Table or Flat Table? RRS feed

  • Question

  • Hi All,

    In a DB model I am designing I have the need to build a Table of Contents structure.  The issue I am having is the levels within the TOCs can vary by Document so I am trying to figure out the best approach to tackle this.  I can build a recursive table, but I would need to spit out XML from that table back to our UI so we could display the available TOCs...using common table expressions and for xml could work, but that seems like it could get hairy.

     

    A flat table would make it easier to query and build the xml, but then it wouldn't be as extensible?

    Any thoughts would be greatly appreciated...Thanks!

     

    --B

    Wednesday, August 11, 2010 6:30 PM

Answers

  • Hello,

    Can you explain what do you mean by spit out XML from the table back. If I would design then I would decide to go on with a recursive table with a parent-child relationship (self join) table with a ParentKey with in the table. This design is basically a solution for handling ragged hierarchy in data warehousing systems.Ragged Hierarchy has an unbalanced structure and unbalanced level, so a recursive table solves all our purposes in querying and displaying them in the UI using the With CTE command syntax.

    You can approach the same principles as well.

     


    Chandu - SQL Server 2005 - Please mark posts as answered where appropriate.
    Wednesday, August 11, 2010 7:03 PM
  • Hi,

      Slef Reference table is good for this type of requirements .. what is the process that you are following, to generate xml out of the table data.. are you using FOR XML AUTO etc options or any other approach...

    -- Reddy Balaji C.

    Thursday, August 12, 2010 8:38 AM

All replies

  • Hello,

    Can you explain what do you mean by spit out XML from the table back. If I would design then I would decide to go on with a recursive table with a parent-child relationship (self join) table with a ParentKey with in the table. This design is basically a solution for handling ragged hierarchy in data warehousing systems.Ragged Hierarchy has an unbalanced structure and unbalanced level, so a recursive table solves all our purposes in querying and displaying them in the UI using the With CTE command syntax.

    You can approach the same principles as well.

     


    Chandu - SQL Server 2005 - Please mark posts as answered where appropriate.
    Wednesday, August 11, 2010 7:03 PM
  • Thanks Chandu,

     

    So for example if I have a table that looks like this

     

     

    ID | TOC_ID | TOC_SECTION | TOC_DESCRIPTON|PARENT_ID
    1 |I|Chapter|My Test Chapter|-1
    2|A|Section|My Test Section|1
    3|1|SubSection|My SubSection|2

     

    I would want to use for xml to build an xml output for the TOC data so i could send it back to my UI...something like below.  I am just trying to figureout how I could build the SQL so I could output the xml.  If I know the predifined levels then I can write the SQL to generate the below, but trying to generate the xml structure below without knowing how many levels to go for the xml can be tricky...I would also have to use the Section Data out of the recursive table to set the value attribute in the xml.  

     

    XML Attributes:
    ID = Appended values from the TOC_ID field
    value = value from TOC_Section field

    <FilterData>
     <Filter id="I" value="Chapter">
     <Description>My Test Chapter</Description>
      <Filter id="I_A" value="Section">
       <Description>My Test Section</Description>
       <Filter id="I_A_1" value="SubSection">
         <Description>My Test SubSection</Description>
       </Filter>
      </Filter>
    </Filter>
    </FilterData>
    

     

    Thanks again for your info...just really tryign to figure out my approach to getting the data in xml once I have established the table structure.

     

    --B

    Wednesday, August 11, 2010 7:32 PM
  • Hi,

      Slef Reference table is good for this type of requirements .. what is the process that you are following, to generate xml out of the table data.. are you using FOR XML AUTO etc options or any other approach...

    -- Reddy Balaji C.

    Thursday, August 12, 2010 8:38 AM
  • Hi Reddy,

     

    I am using or want to try to use FOR XML PATH so that I can easily define the structure....just not really sure how to do that with my current approach. I will look into Slef Ref Tables.....thanks

     

    --S

    Thursday, August 12, 2010 2:40 PM