Managing deep hierarchies with RDBMS

Trees and graphs

One time or the other you might already have or you will come across a scenario where you have to store deep hierarchies in a relational database.

Some such scenarios are product categories, threaded comments, folder structures which might dynamically increase or decrease.

Products -> Electronics -> Phones And Tablets -> Brands-> Moodels

These hierarchies may change over time, the depth may increase or decrease.

For eg, the products hierarchy may change over time as new categories are introduced.

The above example shows a threaded comment from hacker news. You will notice the hierarchy which keeps on increasing endlessly.

How will you store all these comments in database and again fetch them in the same manner?

How do we manage such deep dynamic hierarchies in a relational database?