Information related to Node in a Database Structure for Tree Data Structure

Question

Hello,

Imagine you have a tree data Structure in a database like this:

Level:    
 id   nextlevel    desc
  1         2       company
  2         3       department
  3         4       group

Nodes:
  id    level   parentnode
  0       1         null    -> the company
  1       2           0     -> dep 1
  2       2           0     -> dep 2
  3       3           2     -> group 1.1
  4       3           2     -> group 1.2

I want to store information about every level. That is, information about the company, departments and groups. ¿How would you do that?

I think I could add a column to the Level table where store the table related. The table would be like this:

Level:    
 id   nextlevel    desc           table
  1         2       company       company
  2         3       department    deparments
  3         4       group         groups

And then create those tables:

Company:
  id level     desc            etc...
  1    1    Acme Company

I've been thinking about a better and more elegant solution but I don't find any. Can someone help me? Is this a good solution?

Regards.

Answer 1

The relational way would be to have separate company, department, and group tables. If you want to store different elements in the company, department, and group tables, then they should be separate tables.

The company table would just have a company ID field as the primary key. You would have one row, or in the case of a conglomerate, multiple rows.

The department table would have a department ID field as the primary key. The company ID would be a foreign key.

The group table would have a group ID as the primary key. The department ID would be a foreign key. The company ID could be a foreign key if there are multiple company ID rows.

To get all of or some of the company, department, group entities, you would do a join on the primary keys and their associated foreign keys.