sql - How can I efficiently store and retrieve tree-structured data in a database? -


i'd write note-taking application browser. user has account, , can create folders, subfolders, subsubfolders, , on, , notes go anywhere in folder structure.

i'm having trouble imagining efficient way store , retrieve information, though (both on server , ideally cached within user's browser), , googling information has left me little confused. complex problem of information out there implies, or missing something?

would out of question simple select * user_id = ?, send user, , have client-side code construct tree full flat list of notes , folders?

your idea possible, not efficient. imagine have user 10.000 notes , subfolders. if use query, everytime user logs in, of information downloaded - if wants edit 1 note.

i think best way it, give each folder , note unique id , parent_id field. if user creates subfolder, subfolder gets own id , parent_id unique id of parent folder. if notes /folders on top level (have no parent) can assign special parent_id identify them.

doing way has advantages: can load data if user needs it. if user doesn't open folder, there no need download of notes in folder. opens folder, can call select * parent_id = id , retrieve subfolders , notes in folder.


Comments

Popular posts from this blog

php - Vagrant up error - Uncaught Reflection Exception: Class DOMDocument does not exist -

vue.js - Create hooks for automated testing -

Add new key value to json node in java -