I'm developing an application where users can create certain business objects (for simplicity let's just call them 'objects') that are stored in table in a relational database (the column names are trivial:
id(int, primary key),
type(varchar), and a few others that are not that relevant).
The operation available to the user through the GUI are typical: create, read, update, list (with filters by name, type, etc).
Now, I need to implement a couple of new operations: the ability to create a hierarchical structure of 'folders' and let the user create, name/rename and move them through this hierarchy. Plus the ability to 'put' the users' objects into any of the 'folders' and filter the list of all objects by the full 'path' to this object (by the names of any of its parent folders).
I'm sure that such tasks have been done by many people in the past, and I'm curious to see what kind of DB structure would be optimal for accomplishing this (especially to filter the objects by their 'full path').