Best design for adding a folder/file relationship

Question

I was given a database that has Projects and Media_files. They were in a simple 1 to many relationship.

I now need to add Folders to Projects. Media_files can be placed in Folders, and Folders can have Folders within them.

I am at a loss as to the best way to, first, have Folders belong to other Folders, and second, the best way to maintain the current relationship between Projects and Media_files, or if its best to break this relationship.

Projects is a simple table:

id, 
title, 
description, 
owner_id. 

Folders are an entirely new table, but my basic idea for them was:

id, 
project_id, 
parent_folder_id, 
title, 
description

Answer 1

If it would help you to be able to maintain the current relationship between Projects and Media_files, you could make "Folders" a kind of Project. The usual way to do this would be to add a parent_project_id to Projects referring to the same table, eg:

create table projects(project_id serial primary key, ...);

alter table projects add parent_project_id references projects;

Then

• Folders are all Projects, or all Projects with a non-null parent_project_id depending on which suits you best
• The old Projects maps to all Projects with a null parent_project_id

Answer 2

I would to it either like this

       Projets
-----------------------
     project_id
       [....]

       Folder
-----------------------
     folder_id
     parent_id (-1 or the matching parent folder)
     project_id
      [....]

      Media   
-----------------------
     media_id
     folder_id
     [....]

This way, you would need to break the relationship for it to work. IF you want to maintain the relationship, I would screw normalisation over, and add a project_id to the Media table as well with proper constraints/trigger so that moving media from one folder to another work without writing the explicit query.

---

or the other way would be:

       Projets
-----------------------
     project_id
       [....]

       Folder
-----------------------
     folder_id
     parent_id (-1 or the matching parent folder)
      [....]

      Media   
-----------------------
     media_id
     folder_id
     project_id
      [....]

You maintain the original relationship and are good to go.