Storing hierarchical data in a database

 

I need to store hierarchical data retrieved from an XML file into a relational database (SQL Server).The data has the following structure in the XML:

Item

  • ItemId (unique identifier)

  • ItemName

  • Other fields describing an item

    • Item Relationships

  • ParentItemId

  • ChildItemId

  • RelationshipType (domain-specific informations that describes the relationship)

  • Other domain specific fields that describe the relationship

    • I want to store this data in the DB such that it will be fast and simple to query. As updates to the relationships won't happen often, I'm not too concerned about update/insert/delete performance.

    I had considered hierarchyid approach. However there are additional fields associated with a relationship besides the parent and child id (ie RelatiopnshipType and a few others) so this doesn't seem feasible.

    How about having two tables named Items and ItemRelationships?

  • The Items table has columns ItemId (the primary key) and ItemName.

  • The ItemRelationships has columns ParentItemId, ChildItemId, RelationshipType.

    • ParentItemId and ChildItemId are foreign keys of Items.ItemId column. Is this an efficient structure for querying? Bare in mind that a ParentItemId can have multiple ChildItemId's. The depth of the hierarchy could be over 10 levels. I'm also confused as to whether this approach is referred to as adjacency list or bridge table or some other term?

    链接地址: http://www.djcxy.com/p/16796.html

    上一篇: 如何防止PYTHON中的SQL注入

    下一篇: 将分层数据存储在数据库中