0

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)

2
  • 1
    This sounds like a classic hierarchical problem. Does this help answer your question?. If I get time later, I'll basically re-write that answer but catered towards your use case here. Though hopefully that gets you on the right path.
    – J.D.
    Sep 23 '21 at 20:56
  • 1
    @J.D. thank you for your comment. I think it set me on the right track. I have to think more about how to iterate over the tables and subsequently build that heirarchy visually in Qt's Tree Widget. If you have some input on that, would really appreciate it. Sep 23 '21 at 21:13
0

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.

1
  • Thank you for your answer. I myself care a lot about speed since in this specific case, I am the user. The application is for me. I seriously did no think of what you proposed, but I am thankful you did. Will try it the way you proposed. Thanks! Sep 24 '21 at 13:13

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.