5

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
3

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
  • 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
3

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.

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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