Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

2 Answers
2

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:

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.