Solved

What is the best way to design a database representing a treeview?

Posted on 2004-11-02
1,008 Views
Last Modified: 2010-05-18
Hi,

I want to build an application that will allow users to move objects around similar to the Windows explorer window w/ folders and files.  What I need is to let users create top level objects (like folders in the root directory) and additional objects beneath it.  Now each objects can have other objects beneath it (like subfolders or sub directories).  There is no set limit on the number of objects allowed or how deep the tree should be.  If the attributes of these objects are stored in a database, what is the best way to design the database that allows maximum flexibility?  

For starters, I will need a table that will store the id of each object and the id of its parent (and possibly the level in the tree it resides in).  Now my question is how can I store the attributes of each object assuming that the object could have different set of attributes.  For example, some objects could have color (and other don't) and some objects could have price (while other don't).  I thought of using a denormalized table where you would have fieldnames:

objectId, fieldname, fieldvalue

where from a single table, it can construct many other tables (say through and sp to return a view), where fieldname is user-defined.  

Although this allows flexibility, it would be an enormous table and obviously, performance will be an issue.  In addition, extra coding is necessary just to read all the attributes for an object.  This will be quite a bit of overhead.

I'm sure this has been done many times before so I'm not reinventing the wheel.  Windows Explorer or any file systems would need this sort of features.

Does anyone have a better solutions to this?  Thanks.
0
Question by:skip1000
    7 Comments
     
    LVL 7

    Assisted Solution

    by:wael_tahon
    This is a baisic design for you problem

    Object table:

    ObjectID
    ObjectName
    ParentID    (Allow null and is FK to ObjectId on the same table)

    Properties Table:

    ProsperityID
    ObjectID ( FK to ObjectID on ObjectTable)
    ProsperityName
    Value


    Best Rwegards
    Wael Tahon
    0
     
    LVL 29

    Assisted Solution

    by:MikeOM_DBA
    You need a good data model, choose one here:

    http://www.databaseanswers.org/data_models/index.htm


    0
     
    LVL 3

    Accepted Solution

    by:
    If there are going to be a standard "set" of attrbutes and then the objects will have any number of these then I would use a table to store all of these attributes (which you can add to as you see fit) and then a table between the objects table this table.

    As wael_tahon has stated:
    Object table:
      ObjectID
      ObjectName
      ParentID    (Allow null and is FK to ObjectId on the same table)

    Attributes table:
      AttributeID
      AttributeName

    ObjectAttribute table:
      ObjectID (FK to Object table)
      AttributeID (FK to Attribute table)

    The tables are the normalised and you'll be storing less data as you won't be repeating the name of the attributes. It will also make it possible to search/group on attributes as they will share a common key. It also allows an object to neatly have more than one attribute of the same type and you can be sure that they will actually be the same and not end up with "user name" and "username", as an example.
    0
     

    Author Comment

    by:skip1000
    Hi cheeky-monkey,

    Is the ObjectAttribute table only used as a composite table and not for storing the values of each attributes?  The attributes would pertain to the fields of the tables.  Also, any ideas on how I can effectively store different datatypes for the different fields?  For example, it is probable some field values would have more than 255 characters, say to store data from a textarea such as this.  Thanks.
    0
     
    LVL 4

    Expert Comment

    by:chanito
    If you feel up for a challenge take a look at Celko trees, this is a very efficient model but very abstract so it can be a pain in the ass to handle.

    http://forums.devshed.com/t64446/s.html

    You have to register sorry...
    0
     
    LVL 4

    Assisted Solution

    by:chanito
    The link I gave first is wrong, although it mentions the link to the article I wanted to show, but here's another link that doesn't need registration.

    http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=1012033
    0
     

    Expert Comment

    by:chambeap
    I've taken a look at some of the solutions in the link and they seem quite complicated for this problem. I've recently developed a tree view based application and I've encountered some of the expected difficulties with this problem.

    I've developed my code in VB.NET with an Access back end.

    I do now have a working solution which uses a few key principals:

    1) The database structure is contained within a single table using the following as key reference fields:

    TV_ID                   (Autonumber)

    This is a unique indent for the treeview node, beit a folder or a document in the tree (I'm using the tree to display both).

    TV_PARENT         (Number)

    This is a reference to the TV_ID that the node is directly attached to

    TV_NAME            (Text)

    This is the display name of the treeview node.

    TV_LEVEL           (Number)

    This is the level in the hierarchy that the node exists- I added this recently to accommodate the free moving of nodes from one level to another, or even adding a node after the tree is build and then associating earlier nodes to this later node.

    TV_TYPE             (Number)

    I'm displaying both folders and documents in the tree, but for the purposes of my tree I'm assigned a value of "0" to all Folder type nodes, and various values to the different document types. This is useful when sorting the tree to get the folders to be extracted first from the database.

    Using these 5 data elements I've managed to construct some fairly complicated tree structures which are stored, updated and written to the database based on events performed on the tree:

    2) Displaying the tree

    I found that the order which I extracted the information from my treeview database table helped with the efficiency and structure when building the tree onscreen (I've attached the SQL I use to extract the tree).

    When I load the tree I attribute each of the nodes with the following:

    NODE.TEXT = TV_NAME
    NODE.NAME = TV_ID
    NODE.TAG = TV_TYPE

    And the Parent and level tag information is already held in the correct locations when the tree is loaded, I can then use the PARENT and LEVEL details when I'm writing the information back out to the database.

    3) Actions on the tree (i.e. Add, Delete, Move)

    I've using some of the following as key points to affect the database based on the tree:

    AFTERLABELEDIT (for any insert / update operations)
    DRAGDROP (for move / copy operations)

    And the key thing I found with the drag drop or delete operations is to remember to iterate the operation down to child nodes where required- I've attached an example of how I recursively delete child or change the level of nodes from specific node locations.

    Hope that helps a bit- if you do need any more information please don't hesitate to contact me.



    query = "select tv_id, tv_name, tv_parent, tv_type from tblTreeView order by tv_type, tv_level, tv_name"
     
     
    
        Private Sub Remove_treenodes(ByVal n As TreeNode)
    
            Dim aNode As TreeNode
    
            For Each aNode In n.Nodes
    
                delete_record("tblTreeview", "tv_id=" & aNode.Name)
    
                aNode.Remove()
     
    
                Remove_treenodes(aNode)
    
            Next
     
    
        End Sub
     
     
    
        Private Sub update_level_treenodes(ByVal n As TreeNode, diff As Integer)
    
            Dim aNode As TreeNode
    
            For Each aNode In n.Nodes
    
                Dim currlevel = get_data_element("tbltreeview", "tv_level", "tv_id=" & aNode.Name)
    
                Dim newlevel = currlevel + diff
     
    
                update_record("tblTreeview", "tv_level=" & newlevel, "tv_id=" & aNode.Name)
     
    
                update_level_treenodes(aNode, diff)
    
            Next
    
        End Sub
    

    Open in new window

    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Introduction: Often, when running a query with joins, the results show up "duplicates", and often, those duplicates can be "eliminated" in the results using DISTINCT, for example. Using DISTINCT is simple: just add it after the SELECT keyword, an…
    Database tuning – How to start and what to tune. This question is frequently asked by many people, both online and offline. There is no hard and fast rule-of-thumb for performance tuning, however, before beginning the tuning process one should a…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    This video Micro Tutorial is the second in a two-part series that shows how to create and use custom scanning profiles in Nuance's PaperPort 14.5 (http://www.experts-exchange.com/articles/17490/). But the ability to create custom scanning profiles a…

    846 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

    9 Experts available now in Live!

    Get 1:1 Help Now