SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Hybrid View

  1. #1
    SitePoint Guru pinch's Avatar
    Join Date
    Mar 2005
    Posts
    688
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Relating a database to XML document

    I had previously posed a question about storing a military command hierarchy to a SQL Server database. Since then I have learned that the hierarchy could expand to include more than 3 tiers so I've decided to move the command hierarchy to an XML file. However, I still need to relate some data in my database to that command hierarchy, but I'm not exactly sure how to relate the two.

    My command structure would look similar to:

    <marineforcesreserve>
    <command name="HQ">
    <department name="G6">
    <section name="IDM">
    </section>
    </department>
    </command>
    <command>
    ..
    </command>
    <command>
    ..
    </command>
    </marineforcesreserve>

    In my 'Personnel' table I need to indicate where in the command structure a person falls. So, do I just add columns for each tier in the XML file?

    Personnel
    PersonnelID int PK
    CommandName varchar(8)
    DepartmentName varchar(8)
    SectionName varchar(8)

    Would this be sufficient or am I going down the wrong route?

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    you're talking about this thread, right?

    using what i suggested in that thread, you don't need to store command/department/section in the personnel table. you only need to store the ID of the smallest division the person belongs to.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  3. #3
    SitePoint Guru pinch's Avatar
    Join Date
    Mar 2005
    Posts
    688
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    you're talking about this thread, right?

    using what i suggested in that thread, you don't need to store command/department/section in the personnel table. you only need to store the ID of the smallest division the person belongs to.
    Yeah that's the one.

    So you're saying that I should:

    1. In my XML document, add an "id" attribute to each leaf node where a person maybe reside.
    2. Refer to that "id" in my Personnel table.

    If this is what you're suggesting, I guess the only drawback would be that I wouldn't be able to determine one's 'division' just by looking in the database, I'd have to reference the xml file to determine where they reside.

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    that's not quite what i suggested. i don't think the XML file is needed at all. you can store the command structure in a table, as i suggested in my other post.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  5. #5
    SitePoint Guru pinch's Avatar
    Join Date
    Mar 2005
    Posts
    688
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    that's not quite what i suggested. i don't think the XML file is needed at all. you can store the command structure in a table, as i suggested in my other post.
    Ok, but in my original post I said that I was moving my command structure to XML because it is expected to grow quite large and I've read where DB queries can get quite complex if the hierarchy grows large (more than 3 tiers). Also, because the command is inherently a tree structure, XML just seems to make more sense.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •