Saving a folder structure in a database

[falkencreative]

Hello.  I am writing a PHP/MySQL application to keep my contacts organized - nothing too exciting, mostly just for practice, since I am just getting into PHP/MySQL.

 

Ideally, I would like to be able to organize my contacts into folders, with the idea that I can go back and edit/add/delete folders later if necessary.  Here is my question: what is the best way to record the folder hierarchy?

 

For example, here is a sample folder structure:

 

+ Home Folder

  + Subfolder 1

        -- contact 1

        -- contact 2

  -- contact 3

  -- contact 4

 

My initial idea is to create two tables in the database, one for my contacts, and one for my folders.  I could assign each of the folders a unique ID #, and then have a "Parent" field in both tables to specify which folder that particular item goes in.

 

So, in the above example...

 

The Home Folder would be Folder ID#101, and would have no parent folder, since it is the root folder.

Subfolder #1 would be Folder ID#102, and its parent would be #101

Contact #1 would have no Folder ID (since it is not a folder) but its parent would be folder #102

Contact #3 would have no Folder ID, but its parent would be folder #101

etc....

 

Then, it would just be a matter of using MySQL/PHP to determine the relationships between items based on the Parent field in the database, and generate the correct structure.  I am not worried about preserving the order that these items go in (I had intended to arrange the items alphabetically), but just to make sure that the overall folder structure is correct.

 

Does this make sense?  Is there a more efficient way to do things?  Like I said, I don't know everything there is to know about PHP or MySQL, so any ideas you have would be great.  Thanks.

[fenway]

Hierarchal data is not fun to store... check out the sticky on normalization, there's a link to dev article there.