Question regarding use of a table to store temporary data

[knowNothing]

This question kindof extends the project described at [url=http://www.phpfreaks.com/forums/index.php/topic,111785.0.html]http://www.phpfreaks.com/forums/index.php/topic,111785.0.html[/url] but is actually a different question.

I need to be able to have a teacher approve a file upload before a student can actually upload it to a database that's usable by that particular student.  But sometimes the teacher won't be able to approve (however many files from however many students) until later that night or even possibly the next day.  So obviously, the files need to be uploaded to a temporary location until approved, or at least flagged as 'unapproved' in the database.  I was thinking of the following:

- Student uploads file (blob) to a mysql table (named something like temp_filespace).
- Teacher gets a notification that there are files that need to be approved.
- Teacher logs in and approves files
- Once approved, the file (blob data) is moved to a new table that has the necessary functionality (permissions) for students to be able to download (ie. public db table, as opposed to the temporary table that has no other functionality than storing the blob temporarily)

Is there a better or more efficient way of accomplishing this?  I was thinking either this way, or storing it all in a single table and just flag the blob data as either unapproved or approved, and parse it accordingly.  Would one way take more code in the long run?  I'm having trouble thinking about the differences in code length for either of these options.

For my own purposes I'll do a small layout:

---------------------------
MySQL Table1 (public)
---------------------------
file1_approved
file2_approved
file3_approved

---------------------------
MySQL Table2 (temporary)
---------------------------
file1_unapproved
file2_unapproved
file3_unapproved


----------------------------OR------------------------------


---------------------
MySQL Table (public)
---------------------
file1_unapproved
file2_unapproved
[b]file3_approved[/b]
file4_unapproved

[knowNothing]

I was thinking that 2 tables might be better.  This way, I could keep all unapproved data for later, further examination.  If approved, I could insert it into the public DB and keep the existing unapproved file in the temp DB and flag it as 'processed'.  This way I have a paper trail for better logging if need be.

I'm just trying to plan for future unrealized functionality.  I MIGHT want a table with all the files that have requested approval, regardless of their approved or unapproved status.

[neylitalo]

One good rule of thumb to follow is this: Never store files in the database. Store them in the filesystem, and then just store the path of that file in the database.

However, to answer your question, I would think one table would be better - except design it something like this:

Replace datatypes with your preference.

file_id int not null autoincrement
owner_id int not null (foreign key to the student's id in the db)
file_path varchar(255) not null

[Ninjakreborn]

This is the idea I used for that
In the table storing file information have approved field.
set it to 0 to default, when they come to view the file, or a chance to view the file, check approved, if it's 0 don't show the file.  If the person approves it, it goes to one, then the file can be shown on the website.

Very simply and all, in all only takes one more field in the database, the advice was given to me by someone on these boards.

[knowNothing]

Why is it a bad idea to store files directly in the database?

[knowNothing]

[quote author=businessman332211 link=topic=112144.msg455474#msg455474 date=1161451307]
This is the idea I used for that
In the table storing file information have approved field.
set it to 0 to default, when they come to view the file, or a chance to view the file, check approved, if it's 0 don't show the file.  If the person approves it, it goes to one, then the file can be shown on the website.

Very simply and all, in all only takes one more field in the database, the advice was given to me by someone on these boards.
[/quote]

I think this does make more sense.  I shall code it accordingly and see how it goes.  Thanks for the ideas.

[Barand]

[quote author=knowNothing link=topic=112144.msg455506#msg455506 date=1161455322]
Why is it a bad idea to store files directly in the database?
[/quote]

To display images you use an HTML img tag with a "src='filename' " attribute. This points to the location of the file on the server. If it is stored in the db you cannot do this without the extra overhead of extracting it as a dynamic image.

[knowNothing]

[quote author=Barand link=topic=112144.msg455765#msg455765 date=1161511485]
[quote author=knowNothing link=topic=112144.msg455506#msg455506 date=1161455322]
Why is it a bad idea to store files directly in the database?
[/quote]

To display images you use an HTML img tag with a "src='filename' " attribute. This points to the location of the file on the server. If it is stored in the db you cannot do this without the extra overhead of extracting it as a dynamic image.
[/quote]

Ok..but.. im not storing images in the db.. im storing PDFs, Word DOCs, powerpoint presentations.. does this matter?