Database schema for tree

Question

I want to create a database schema for a tree. Each record will have a parent except root-record.

tree_table

 id | name 
-------------
  1 |  tree1
  2 |  tree2
  3 |  tree3

The nodes in each tree will be given.

tree_node_table

 id | name | tree_id | parent_id
---------------------------------
  1 |  a   |   1     |   NULL  
  2 |  b   |   1     |    1
  3 |  c   |   1     |    2
  4 |  d   |   1     |    2
  5 |  e   |   1     |    4
  6 |  f   |   2     |   NULL  
  7 |  g   |   2     |    6
  8 |  h   |   2     |    7
  9 |  i   |   2     |    7
 10 |  j   |   2     |    9

I feel it is not optimized one, anybody have better design?

UPDATE: the tree_id is using to identify quickly the tree the record belongs.

The tree_id might lead to headaches once you start moving nodes around from tree to tree. It is redundant, because your parent already defines it. And the parent of the parent also... — Argeman, Nov 07 '12 at 14:09
http://stackoverflow.com/questions/6567385/how-to-represent-a-tree-like-structure-in-a-db/6568146#6568146 — Damir Sudarevic, Nov 07 '12 at 15:15

score 1 · Answer 1 · answered Nov 07 '12 at 13:34

Keep it simple with one table:

create table trees (
  id        integer primary key,
  name      varchar not null,
  parent_id references trees)

This gives you all of the logical elements you need. If you need more for specific optimisations then consider them after you have identified a genuine need for them

score 0 · Answer 2 · answered Nov 07 '12 at 13:28

Leave away the first table, and leave away the "tree_id" column in the second table.

Beside that, your design is quite good. For few databases that don't provide recursive/looping queries there is the "nested set" tree model that is better suited for reading with non-recursive or looping queries, but i would stay away from that - it has extremely terrible performance when changing large trees.

Database schema for tree

2 Answers2