5

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.

Biroka
  • 609
  • 10
  • 23

1 Answers1

7

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.

Peter Lang
  • 54,264
  • 27
  • 148
  • 161
  • Good answer, do store the files in your database, otherwise you might have problems with your files being out of sync with your database if something goes wrong during a change. – Thirler Mar 03 '10 at 18:34
  • Good answer, however no sub-folders supported. – Mecanik Jan 08 '18 at 10:14
  • @NorbertBoros Sorry if I misunderstood your comment, but `folders.parent_folder_id` would be used to create a hierarchical structure of folders. – Peter Lang Jan 08 '18 at 10:20
  • Ok, But lets assume that a usecase requires file /foo/bar/zoo/etc/MyFile.txt. How could we fetch this? How would the sql query look like? Can this be done in a single query or should we fire multiple ones? – Alkis Mavridis Apr 25 '20 at 07:44
  • 1
    @AlkisMavridis: You would ideally do that with a hierarchical query. There are already lots of examples out there, this is an answer for MySQL: https://stackoverflow.com/a/33737203/17343 – Peter Lang Apr 27 '20 at 06:30