This is a classic "tree structure" design.
... each folder record in the database can have a "parent_folder" field ...
One Parent, many Children. That's right.
... those that don't have parent folders have a specific id ...
This id (zero being an obvious choice) would represent the "Root of Everything", which can never be removed and only within which you can create new entries (i.e. you can't have two root nodes).
Such a design should work just fine.
I will have to somehow iterate over this table, add dynamically to my gui (my tree widget) folders and subfolders.
Now; hang on a minute. Do you?
OK, you could populate the whole tree in one go. You suffer a [big], one-time hit loading it all but then clicking around will be really quick. That sounds good.
Except that, over time, your tree could accumulate thousands and thousand of entries, which will take a very long time to iterate through and load into the Tree control.
Two things about Users:
- They're not, generally, noted for their patience.
- They tend to be creature of habit and use the same things repeatedly (rather than wandering around, "randomly" using huge swathes of an Application). They tend to find the bits of the Application that are "useful" to them and then stick with them.
In this context, that means they'll only be interested in "their" tree nodes.
So, alternatively, you could populate the root level tree nodes and leave it at that.
As the User asks to look at what's inside a particular tree node, then you go and get just the rows to satisfy that request. OK, it means more queries for the database and, yes, the User has to wait for each one, but each query will be [much] quicker and the User will only be waiting for things that they're actually interested in (and not the 9,999,986 others that exist in the system, but that this User doesn't really care about).
As long as your parent_id column is properly indexed, this works perfectly well.