Jump to content

Question regarding use of a table to store temporary data


knowNothing

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.