Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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

Posted on 2004-11-02
Medium Priority
Last Modified: 2010-05-18

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.
Question by:skip1000
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Assisted Solution

wael_tahon earned 300 total points
ID: 12473941
This is a baisic design for you problem

Object table:

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

Properties Table:

ObjectID ( FK to ObjectID on ObjectTable)

Best Rwegards
Wael Tahon
LVL 29

Assisted Solution

MikeOM_DBA earned 300 total points
ID: 12475494
You need a good data model, choose one here:



Accepted Solution

cheeky-monkey earned 600 total points
ID: 12489719
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:
  ParentID    (Allow null and is FK to ObjectId on the same table)

Attributes table:

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.
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.


Author Comment

ID: 12490741
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.

Expert Comment

ID: 12495831
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.


You have to register sorry...

Assisted Solution

chanito earned 300 total points
ID: 12500799
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.


Expert Comment

ID: 22211737
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:


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)
    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)
    End Sub

Open in new window


Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

610 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