PDA

View Full Version : Tree and Table Structure



tmunz
22 May 2007, 12:19 PM
Can anyone give me any advice on how to setup a table in the database so it will work with the tree. What I have seen in the examples is either a static external file (dep-tree.json) or the responses from a cfm page or php. Whatever example I have looked at it appears the id is how the nodes are being linked. (id:"source\/locale\/ext-lang-en.js")

My initial thought was to use the adjacency model:
uid (unique PK)
name varchar
parentid (null or uid)

However, (id:"source\/locale\/ext-lang-en.js") looks more like a path. Based off of this it looks like the table structure should be more like:

uid (unique PK)
name varchar
parentid int
nodepath (null or parentid/uid)

What I would like to do have this accomplish is: The Projects would need to be the root, sites and zones can either have cls: folder/file, and the transect can only be a leaf.


Project1 > Site1
> Site2
> Zone1
> Zone2
> Transect1
Project2 > Site1
> Site2
> Zone1
> Zone2
> Transect1




Thanks.

PFM
22 May 2007, 6:02 PM
tmunz.. take a look at http://extjs.com/forum/showthread.php?t=5277&highlight=tree+json

That post helped me early one... I am creating a Feed Reader app that is based on the Feed Viewer example on this site as the front end... mine uses trees in the west and east content panels. I have an administration piece that allows an administrator to prebuild the trees and create readonly nodes for feeds that everyone will see.... Each user will have the ability to add/update/delete.. nodes/feeds (folders and leafs) with their properties..

If your back end is java, take a look at http://www.json.org/java/index.html for converting objects to JSON...


The FEED_TREE_NODE table is the one that maintains the parent/child relationships for the tree nodes.

SQL (Oracle) for tables


REM ASSCADMIN FEED

DROP TABLE "ASSCADMIN"."FEED";
CREATE TABLE "ASSCADMIN"."FEED"
( "FEED_ID" NUMBER NOT NULL ENABLE,
"NAME" VARCHAR2(255 BYTE) NOT NULL ENABLE,
"DESCRIPTION" VARCHAR2(255 BYTE) NOT NULL ENABLE,
"URL" VARCHAR2(512 BYTE) NOT NULL ENABLE,
"URL_QUERY_STRING" VARCHAR2(1024 BYTE),
"HTTP_PROXY_CONFIGURATION" VARCHAR2(50 BYTE),
"IS_ACTIVE" CHAR(1 BYTE) NOT NULL ENABLE,
"LAST_UPDATE_DATETIME" DATE,
"LAST_UPDATE_USER" VARCHAR2(32 BYTE),
CONSTRAINT "FEED_PK" PRIMARY KEY ("FEED_ID") ENABLE,
CONSTRAINT "FEED_UK1" UNIQUE ("NAME") ENABLE
) ;

REM ASSCADMIN FEED_UK1

CREATE UNIQUE INDEX "ASSCADMIN"."FEED_UK1" ON "ASSCADMIN"."FEED" ("NAME")
;


REM ASSCADMIN FEED_TREE

DROP TABLE "ASSCADMIN"."FEED_TREE";
CREATE TABLE "ASSCADMIN"."FEED_TREE"
( "FEED_TREE_ID" NUMBER,
"NAME" VARCHAR2(255 BYTE) NOT NULL ENABLE,
"DESCRIPTION" VARCHAR2(255 BYTE) NOT NULL ENABLE,
"LAST_UPDATE_DATETIME" DATE,
"LAST_UPDATE_USER" VARCHAR2(32 BYTE),
CONSTRAINT "FEED_TREE_PK" PRIMARY KEY ("FEED_TREE_ID") ENABLE,
CONSTRAINT "FEED_TREE_UK1" UNIQUE ("NAME") ENABLE,
CONSTRAINT "FEED_TREE_UK2" UNIQUE ("PREFIX") ENABLE
) ;

REM ASSCADMIN FEED_TREE_UK1

CREATE UNIQUE INDEX "ASSCADMIN"."FEED_TREE_UK1" ON "ASSCADMIN"."FEED_TREE" ("NAME")
;

M ASSCADMIN HTTP_PROXY_CONFIGURATION

CREATE TABLE "ASSCADMIN"."HTTP_PROXY_CONFIGURATION"
( "KEY" VARCHAR2(32 BYTE) NOT NULL ENABLE,
"DESCRIPTION" VARCHAR2(255 BYTE),
"XML" CLOB NOT NULL ENABLE,
"LAST_UPDATE_DATETIME" DATE NOT NULL ENABLE,
"LAST_UPDATE_USER" VARCHAR2(32 BYTE) NOT NULL ENABLE,
CONSTRAINT "HTTP_PROXY_CONFIGURATION_PK" PRIMARY KEY ("KEY") ENABLE
) ;


REM ASSCADMIN FEED_TREE_NODE
DROP TABLE "ASSCADMIN"."FEED_TREE_NODE";
CREATE TABLE "ASSCADMIN"."FEED_TREE_NODE"
( "FEED_TREE_NODE_ID" NUMBER NOT NULL ENABLE,
"USER_ID" VARCHAR2(32 BYTE),
"FEED_TREE_ID" NUMBER NOT NULL ENABLE,
"PARENT_FEED_TREE_NODE_ID" NUMBER,
"SORT_ORDER" NUMBER NOT NULL ENABLE,
"TEXT" VARCHAR2(255 BYTE) NOT NULL ENABLE,
"LEAF" CHAR(1 BYTE) NOT NULL ENABLE,
"ALLOW_DRAG" CHAR(1 BYTE) NOT NULL ENABLE,
"ALLOW_DROP" CHAR(1 BYTE) NOT NULL ENABLE,
"ALLOW_EDIT" CHAR(1 BYTE) NOT NULL ENABLE,
"QTIP" VARCHAR2(512 BYTE),
"FEED_TREE_NODE_CSS_CLASS_ID" NUMBER,
"FEED_ID" NUMBER,
"URL" VARCHAR2(512 BYTE),
"IS_ACTIVE" CHAR(1 BYTE) NOT NULL ENABLE,
"LAST_UPDATE_DATETIME" DATE NOT NULL ENABLE,
"LAST_UPDATE_USER" VARCHAR2(32 BYTE) NOT NULL ENABLE,
CONSTRAINT "FEED_TREE_NODE_PK" PRIMARY KEY ("FEED_TREE_NODE_ID") ENABLE,
CONSTRAINT "FEED_TREE_NODE_FEED_FK1" FOREIGN KEY ("FEED_ID")
REFERENCES "ASSCADMIN"."FEED" ("FEED_ID") ENABLE,
CONSTRAINT "FEED_TREE_NODE_FEED_TREE_FK1" FOREIGN KEY ("FEED_TREE_ID")
REFERENCES "ASSCADMIN"."FEED_TREE" ("FEED_TREE_ID") ENABLE,
CONSTRAINT "FEED_TREE_NODE_FEED_TREE__FK1" FOREIGN KEY ("FEED_TREE_NODE_CSS_CLASS_ID")
REFERENCES "ASSCADMIN"."FEED_TREE_NODE_CSS_CLASS" ("FEED_TREE_NODE_CSS_CLASS_ID") ENABLE,
CONSTRAINT "FEED_TREE_NODE_FEED_TREE__FK2" FOREIGN KEY ("PARENT_FEED_TREE_NODE_ID")
REFERENCES "ASSCADMIN"."FEED_TREE_NODE" ("FEED_TREE_NODE_ID") ON DELETE CASCADE ENABLE
) ;




REM ASSCADMIN FEED_TREE_NODE_CSS_CLASS

DROP TABLE "ASSCADMIN"."FEED_TREE_NODE_CSS_CLASS";
CREATE TABLE "ASSCADMIN"."FEED_TREE_NODE_CSS_CLASS"
( "FEED_TREE_NODE_CSS_CLASS_ID" NUMBER NOT NULL ENABLE,
"NAME" VARCHAR2(255 BYTE) NOT NULL ENABLE,
"DESCRIPTION" VARCHAR2(255 BYTE) NOT NULL ENABLE,
"LAST_UPDATE_DATETIME" DATE,
"LAST_UPDATE_USER" VARCHAR2(32 BYTE),
CONSTRAINT "FEED_TREE_NODE_CSS_CLASS_PK" PRIMARY KEY ("FEED_TREE_NODE CSS_CLASS_ID") ENABLE,
CONSTRAINT "FEED_TREE_NODE_CSS_CLASS_UK1" UNIQUE ("NAME") ENABLE
) ;

tmunz
23 May 2007, 4:01 AM
Thanks PFM. It appears your table structure is what I initially had in mind. I started second guessing after reading an article about sql hierarchies and the different types of hierarchy models (Adjacency and Nested Sets).

Thanks again.

PFM
23 May 2007, 8:24 AM
you're welcome... if you come across something and want to know if or how I addressed it, just let me know. I will be glad to help.