CODEWITHSUNDEEP
database-designtreedatabase-schema
Mohammed H
Mohammed H

Reputation: 7048

Database schema for tree

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.

Upvotes: 1

Views: 844

Answers (2)

David Aldridge
David Aldridge

Reputation: 52356

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

Upvotes: 1

Argeman
Argeman

Reputation: 1353

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.

Upvotes: 0

Related Questions