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

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.
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.


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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

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

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…
Suggested Courses

594 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