Jump to content

Archived

This topic is now archived and is closed to further replies.

knowNothing

Question regarding use of a table to store temporary data

Recommended Posts

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
[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.

Share this post


Link to post
Share on other sites
[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.

Share this post


Link to post
Share on other sites
[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?

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.