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