What structure to use in database for virtual file system?
filesystems, mysql, sql
Solution
You could use a structure like the one below.
Alternatively you could store your files within the database in some sort of `BLOB` (`MEDIUMBLOB`).
users
id
name
...
projects
id
name
...
user_projects
user_id
project_id
folders
id
name
project_id
parent_folder_id
...
files
id
filename
parent_folder_id
name_in_filesystem
...
You have a list of `users`, and a list of `projects`. The `user_project` table allows you to assign several users to a project, and to assign several projects to a user.
Every `folder` belongs to one project, and can have a `parent_folder_id` to allow hierarchies. Every file has a `parent_folder_id`, which is the reference to the folder that contains it. The `name_in_filesystem` is the random name that you use to store the file.
Problem
I want to make a webapplication in witch there will be some users with different projects (like c project, c++ project so on). My question is that how should I build up my 3 tables: users,projects,files? One user can have many projects (there will be shared projects too), the projects can have folders(packages) and files, but on the file system I don't want to make that folder hierarchy only pointers to the specific file from database. I.e. I am user1 and I have project1 with 3 folders in it: headers,resource,source and in each folder or root there are several files. The thing is I want to get all the project-related data from user1 and put it in a tree-view, but on the server all the files are in the same folder with randomly generated names.