What is the best way to design a database representing a treeview?
Posted on 2004-11-02
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.