Design best database for connect tree

Suite and Optimus Suite 5
#1

Hi,

I would like to know, how I should design database to use Connector on Tree and TreeGrid. My project requires that user may drag tree nodes or children, add/edit/delete items, and save his changes to database.

To do in best way, should I store XML in the mySQL database? Or should I design tables in the database? Would the table look like this example?

+-------------+----------------------+--------+
| category_id | name                 | parent |
+-------------+----------------------+--------+
|           1 | ELECTRONICS          |   NULL |
|           2 | TELEVISIONS          |      1 |
|           3 | TUBE                 |      2 |
|           4 | LCD                  |      2 |
|           5 | PLASMA               |      2 |
|           6 | PORTABLE ELECTRONICS |      1 |
|           7 | MP3 PLAYERS          |      6 |
|           8 | FLASH                |      7 |
|           9 | CD PLAYERS           |      6 |
|          10 | 2 WAY RADIOS         |      6 |
+-------------+----------------------+--------+

Please advise me on which way I should begin? Thanks!

#2

a) using table with special design is prefferable solution. It is more flexible.
b) the above structure of table is valid, except of first line, it must be

1 | ELECTRONICS | 0

0, not the null, as parent

for such table, connector’s command will look as

render_table(“treetable”, “category_id”, “name”,"",“parent”);

#3

My project is like this:

  1. User logs in.
  2. User can create a new project.
  3. Each new project is a tree (and treegrid, later).
  4. The tree is built and edited by user.
  5. The tree is saved to mySQL.
  6. The user can see tree when login again, or he can make another new project.

How should my database look for this?
What is best method to save many, long trees?
Does each tree need a separate table in database?

Thanks!