I need help creating a database structure for a file management system where users can organize their content in folders and subfolders, just like in cloud storage services.
My main requirements are:
Users should be able to create multiple levels of folders
Files can be placed inside any folder
Each user should only see their own folder structure
Easy retrieval of folder contents and hierarchy
I looked at some existing directory table designs but they seem too complicated for filtering by user accounts. What would be the best approach for the database schema? Should I use a single table with parent-child relationships or separate tables for users, folders, and files?
Any suggestions for handling the nested structure efficiently would be appreciated.
closure tables r perfect for this. u keep an ancestor-descendant tbl next to your main folders tbl. takes more storage but queries r lightning fast and no recursive headaches. ive used this for years - scales great even with really deep folder trees.
I used three separate tables - users, folders, and files. Folders have user_id, parent_folder_id, and name columns. Files link to both user_id and folder_id. This setup makes permissions way cleaner and indexing much better. Don’t store full paths in your database - I learned this the hard way. When users rename folders buried deep in the tree, everything breaks. Instead, I use a recursive CTE in PostgreSQL that builds paths on the fly. MySQL folks can do the same with stored procedures. Make sure you’ve got proper foreign key constraints and index the user_id + parent_folder_id combos for decent performance.
In a similar project, I implemented a single table for folders with a self-referencing foreign key for parent-child relationships. Each folder is associated with a user via user_id to ensure privacy, and root folders have a null parent_id. To enhance performance, I added a path column for quick retrieval of the hierarchical structure. Utilizing database triggers to maintain this path upon any changes proved beneficial. Filtering by the parent_id and user_id allows efficient access to folder contents. This setup has worked effectively, managing thousands of folders with ease while keeping the architecture simple.