What structure to use in database for virtual file system?

Question

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.

Answer 1

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.