skip1000
asked on
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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...
http://forums.devshed.com/t64446/s.html
You have to register sorry...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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
ASKER
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.