Take the 2-minute tour ×
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.

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
share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

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
share|improve this answer
    
This method works best for my project. It saves a bunch of re-writes in existing code. And, as always seems to be the case, speed is of the essence in this project. –  ursasmar May 30 '11 at 4:51
add comment

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.

share|improve this answer
1  
"(-1 or the matching parent folder)" why not NULLABLE? NULL or the matching parent folder. Then you can add a foreign key constraint. –  edze May 29 '11 at 17:23
    
Also possible, it depends more on your liking of "NULL" values. –  DrColossos May 29 '11 at 18:10
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.