Storing file structure in PHP

Question

What is the best way to store a file structure, along with files, say from a Zip file? Currently each file is a row in the MySQL database, but obviously no structure can be read from that. I want to avoid storing a binary file. All files should be plain text. Each zip/file structure is private to the user whom submitted it.

Answer 1

If you want structure, you could have a directory table and a file table. Then for each directory in the root would have a parent id of 0. And then each directory under that have the parent id of it's parent.

Then you give the files directory id's to indicate where they are.

Answer 2

Easiest way (perhaps not the best) is to determine the 'root' folder of the directory tree you are after, then upon each file take the path relative to that folder and store it as another column (e.g. 'path')in the db table. You can later look upon these values to build the same directory structure.

Answer 3

Best is contextual. But taking it on face value, I would say that the best way to store a file structure would be in a file system. Unless you need to link entries together with other records or otherwise query on them, a relational database isn't the best choice for arbitrary, hierarchical data. You can also go with a hybrid solution, where you keep an index in the database, with the file-path and perhaps some meta data, but still store it in the file system.

Answer 4

My recommendation would be similar to that of Ólafur Waage, though I would use a single table, with indicator columns to denote the 'type' of record.

id - Primary Key
parent_id - FK to self (id)
type - enum('D', 'F')
name - varchar
content - BLOB (if you wanted to ever store the file contents).

This follows a hierarchical tree structure, which, for MySQL you can find additional information here: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html