Best design for adding a folder/file relationship 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
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.