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), name(varchar), 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').

  • 3
    you might find this topic helpful to make an appropriate choice – xmike Mar 31 '17 at 8:49
  • Wow, that's a really comprehensive post. – Robert Harvey Apr 1 '17 at 0:44

Regardless of what technology you use for the persistence, you need to clearly define the capabilities of the hierarchy in domain terms to the client or user.

File systems do this and in my opinion, generally with undesirable limitations. For one, the dramatically restrict the characters allowed in a folder ir path element. For another, they usually work poorly when the user wants to use folders like categories, which is to say, wants to put one item in two folders.

In short, you should first work thru what you want to provide the client at the api level, the can map those capabilities to the db.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.