I don't think saving the files on the disk and just storing their name, and possibly path, is going to create any more programming than if you stored the files as BLOBs inside the database; this goes for both PHP and .NET.
In both cases you need to detect the mime type of the file when it is uploaded and save that, so you're not losing or saving any time there.
In both cases you have to gather the contents of the file and dump it back to the client, setting headers before you do. You have
maybe two or three lines of extra code if the file is stored on disk because you have to fopen, fpassthru, fclose, but that's it.
It doesn't even really add extra work in the case of saving revisions or file history.
jobsid
name
draftsid
job_id
name
draft_revisionsid
draft_id
mime_type
orig_name
I'm assuming a structure where you have multiple jobs and each job has multiple drafts; as such, the jobs and drafts tables are fairly self explanatory. The interface that I imagine is one where the user selects a project and is given a list of drafts; they select a draft and are given a list of revisions for that draft; clicking on a particular revision serves that file.
Let's save the uploaded files in:
/home/user/public_html/drafts
Let's add a subdirectory for each project created:
/home/user/public_html/drafts/<project_id>
We do this only because we might have to inspect the directory manually; we don't want to be bombarded with a billion files plopped into a single directory. There is probably no need to add further subdirectories unless we expect lots of revisions; in that case, I'd suggest subdirectories like so:
/home/user/public_html/drafts/<project_id>/<draft_id>
We store the files in public_html to make them web accessible and serving them easier; we can just provide direct paths and apache can serve the file without invoking PHP. If the files are sensitive, you will want to NOT put them in public_html, but instead place them higher in the directory structure and use a PHP script to fetch their contents (like if they had been saved as a BLOB).
A couple of notes about the draft_revisions table; it looks like its missing some fields! Actually, it's not. It has everything we need right there. If the id column is an auto_incrementing primary key, it is guaranteed to be unique; thus when saving files to the file system we can use this id. When files are requested, we will know which project, draft, and revision they are seeking; since we have a predetermined path layout, this provides us with everything we need to load the file.
For example, a user creates the first project in the system, calling it test. They then create a draft, named a_draft, and upload the file word.doc. They then upload a revision, calling it word2.doc.
Tables+------+-------------+--------------+
| jobs | id | name |
+------+-------------+--------------+
| | 1 | test |
+------+-------------+--------------+
+--------+------+---------+------------+
| drafts | id | job_id | name |
+--------+------+---------+------------+
| | 1 | 1 | a_draft |
+--------+------+---------+------------+
+-----------------+------+-----------+------------+-----------+
| draft_revisions | id | draft_id | mime_type | orig_name |
+-----------------+------+-----------+------------+-----------+
| | 1 | 1 | ms word | word.doc |
+-----------------+------+-----------+------------+-----------+
| | 2 | 1 | ms word | word2.doc |
+-----------------+------+-----------+------------+-----------+
Our files will be stored here:
<i>word.doc</i>: /home/user/public_html/drafts/1/1
<i>word2.doc</i>: /home/user/public_html/drafts/1/2
You might be wondering how we determine which revisions are newest and which are oldest for a particular draft; you can add a DATETIME field or, since the id column is an auto_incrementing primary key, you can assume lower IDs represent older files (if you don't need the exact time it was uploaded).
Some PHP:
<?php
function list_drafts($job){
$Clean = Array();
$Clean['Job'] = // Clean the variable $job
// We want numbered output
mysql_query("SET @numbering = 0");
// Get the drafts from the job
$sql = "SELECT "
. "id, name, @numbering := @numbering + 1 AS row "
. "FROM drafts WHERE job_id={$Clean['Job']} "
. "ORDER BY id";
$q = mysql_query($sql);
// Now process $q
}
function list_revisions($draft){
$Clean = Array();
$Clean['Draft'] = // Clean the variable $draft
// We want numbered output
mysql_query("SET @numbering = 0");
// Get the revisions from the draft
$sql = "SELECT "
. "r.mime_type, r.orig_name, "
. "@numbering := @numbering + 1 AS row, "
. "CONCAT( 'drafts/', d.job_id, '/', r.id ) AS file "
. "FROM draft_revisions r, drafts d "
. "WHERE "
. "d.id={$Clean['Draft']} AND "
. "d.id=r.draft_id "
. "ORDER BY r.id";
$q = mysql_query($sql);
// Now process $q
}
?>
