Vmusic

Karen,
What you have is a hierarchical relationship. A file can (at one time) be in only one folder, or have one parent.

Even if I copy a file, so it's in two folders, it's really a different instance of the file.

This hierarchical relationship can be defined through recursion. I might suggest something like:

table file
file_id (auto increment), file_type, file_nm, file_extension, file_description, creation_dt, creation_userid, last_update_dt, last_update_userid, parent_id

If I have a file 'myfile.txt' in a folder or directory called MyDocuments, then I have two rows in the file table

One row for the MyDocuments folder or directory, where the file_type might be 'DIRECTORY' (as opposed to 'FILE'), and the file_exention and parent_id would be NULL. Here the file_id = 23

One row for the 'myfile.txt' where the file_type is 'FILE', and the file_id here is 37 and the parent_id =23.

NOW.... If I move this file to another folder, then I have to change the parent_id.


I have done some of this in php and MySQL for web sites... it's a little trciky because you have to be sure every piece of code that can change your files also updates your database. This is a GREAT WAY to capture additional meta data about your files however.

Hope that helps!!!
Vmusic