Im going to create a design for an application that looks like a Google drive, anyone can create files on the root directory or creating inside folders (and subfolders)
Now on the user's console, I have to show all the top level folders, a down arrow will be there, it the folder contains any subfolder, then if we click the down arrow, all the subfolders inside the folder will be visible.
Sample:
Im not sure, what kind of Table design I should choose for this, can someone help me with this?
You can create a self referencing hierarchical table for that. There will be parent_id column to store the id of parent folder. For top level folder parent_id will be null.
Below can be the list of columns for your table.
1. id -- integer auto increment primary key.
2. Name -- Name of the folder
3. Parent_id -- id of parent folder. Referencing id column of this table. Null for top level folders.
4. Sequence_Number -- In which sequence folders will be displayed. Folder with same parent_id will be displayed order by seqeunce_number. You can change it from front end so that user can choose to rearrange folder sequence.
5. description -- description of a folder. If you want to put it.
6. user_id -- Foreign key to user table.