Tree in mysql

[samikhan83]

hi...
i am designing the database for an inventory system while designing it forming tree like structure.... so i am unable to implement tree in database so can anyone help with this....

its like category->sub-category->sub-sub-category......

THANX IN ADAVANCE

[r035198x]

hi...
i am designing the database for an inventory system while designing it forming tree like structure.... so i am unable to implement tree in database so can anyone help with this....

its like category->sub-category->sub-sub-category......

THANX IN ADAVANCE

Just have the sub-sub-categorytable keep a sub-category_ID and the sub-category table have a category_ID ...?

[Atli]

Hi.

Creating a tree structure isn't difficult. Consider this:

CREATE TABLE tree(
    ID int Primary Key AUTO_INCREMENT, 
    ParentID int References tree(ID)
);
 

It simply references itself, the first record having a NULL value for a parent and each new record having a previous record as it's parent, therefore creating a tree structure.

The challenging part is getting your API to read this properly.

[moltendorf]

Personally, I have made various trees, and decided to use two tables to effectively make the trees readable at a fast pace.

The stream table contained 3 columns:
stream_identifier, stream_branch_identifier, and stream_sort

The branch table contained 3 or more columns:
branch_identifier, branch_parent

Each stream contained a sorted list of which branches to follow to get to that one little result.

To then pull out a list of branches to follow to get to that one result I wanted, I'd run a fairly simple SELECT statement:

SELECT *
    FROM `streams` `stream`
        LEFT JOIN `branches` `branch`
            ON `stream`.`stream_branch_identifier` = `branch`.`branch_identifier`
    ORDER BY
        `stream`.`stream_identifier` ASC,
        `stream`.`stream_sort` ASC;

Table branches:

branch_identifier branch_parent
-------------------------------
1                 0
2                 1
3                 2
4                 0
5                 4
6                 5

Table streams:

stream_identifier stream_branch_identifier stream_sort
------------------------------------------------------
1                 1                        2
1                 2                        1
1                 3                        0
2                 2                        1
2                 3                        0
3                 3                        0
4                 4                        2
4                 5                        1
4                 6                        0
5                 5                        1
5                 6                        0
6                 6                        0

If I just wanted to get to the branch with the 1 for its identifier, I could add a WHERE clause, and get a result table as shown below.

SELECT *
    FROM `streams` `stream`
        LEFT JOIN `branches` `branch`
            ON `stream`.`stream_branch_identifier` = `branch`.`branch_identifier`
    WHERE `stream`.`stream_identifier` = '1'
    ORDER BY
        `stream`.`stream_identifier` ASC,
        `stream`.`stream_sort` ASC;

Result:

stream_identifier stream_branch_identifier stream_sort branch_identifier branch_parent
--------------------------------------------------------------------------------------
1                 3                        0           3                 2
1                 2                        1           2                 1
1                 1                        2           1                 0

There may be a different way you want to go about this, but the question you're asking is the exact one I asked myself yesterday when I wanted to get a set of modules out of a table, and a way to get to modules within sub directories.

[samikhan83]

Thanx for ur help guyzz...I REALLY APPRECIATE