How to organize a file system in a database?

Question

Python

I'm making a simple warehouse app. Accordingly, I need to store Sections and Warehouse Elements in the database.
In general, my task resembles storage in the file system database

. Now it looks like this:

There is a FOLDERS table in it, two columns
PATH and NAME

It is needed so that when an application makes a request, for example, to read a folder, it checks whether such a folder exists or not

Example:

read '/'
displays all folders (NAME) with PATH = '/'

I store the files of each folder in a separate table

For example:
The FOLDERS table has
PATH -> '/'
NAME -> 'FOLDER1'

For this folder, respectively, there is a table '/ FOLDER1'

PATH -> '/FOLDER1/'
NAME -> 'SUBFOLDER1'

For this folder, there is a table '/FOLDER1/SUBFOLDER1' respectively.

At first I tried to keep everything in one table and just specify the path for each element of the
warehouse in the 'PATH' column. But there were difficulties in processing options that the
user, for example, deletes a folder (check that the folder already exists, delete all the contents of the folder, respectively, all subfolders and files, etc.)

In general, is it possible to somehow simplify this? Maybe there are already some ready-made solutions?

Answer 1

There are several options for storing hierarchical data in a relational database. The choice depends mainly on customer requests and performance requirements.
If you want to understand the issue and make an informed choice, here is some theory (and practice):
books.google.com/books?id=uw2lq2o4VbUC
https://blog.duncanworthy.me/sql/hierarchical-data...
https:/ /habr.com/ru/post/263629/

Answer 2

Such a structure can be stored in one table by entering an additional field where the parent's id is stored.

Answer 3

Why keep something if it exists?
Give them a file structure