CODEWITHSUNDEEP
postgresqldatabase-designdata-modelinghierarchical-data
TheDataGuy
TheDataGuy

Reputation: 3118

Postgres - Table design to store user wise folder structure

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:

enter image description here

enter image description here

Im not sure, what kind of Table design I should choose for this, can someone help me with this?

Upvotes: 5

Views: 1964

Answers (1)

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15905

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.

Upvotes: 3

Related Questions