Database Design – Implementing Infinite Folders and Subfolders in SQLite

database-designsqlite

So I am building a desktop application using Qt, C++, and SQLite.

I want the user to be able to create as many folders as he likes, and those folders can have "notes" (its a note-taking app) and/or as many subfolders as one wishes there to be.

My question is how to implement something like this in database design?

So for example, each folder record in the database can have a "parent_folder" field with those that don't have parent folders have a specific id, and the others would have the rowid of the folder record that is their parent.

I will have to somehow iterate over this table, add dynamically to my gui (my tree widget) folders and subfolders. Not to mention if I also have a table for notes, each note will have to have an id corresponding to the rowid of its parent folder.

This is a half-baked solution that I haven't really developed well, but it was the first thing that I can come up with. (this is my first question here, please be merciful)

Best Answer

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.

Related Question