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

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.
skip1000Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

wael_tahonCommented:
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
MikeOM_DBACommented:
You need a good data model, choose one here:

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


0
cheeky-monkeyCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

skip1000Author Commented:
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
chanitoCommented:
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
chanitoCommented:
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
chambeapCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.