Jump to content


Photo

Question regarding use of a table to store temporary data


  • Please log in to reply
7 replies to this topic

#1 knowNothing

knowNothing
  • Members
  • PipPip
  • Member
  • 24 posts

Posted 20 October 2006 - 05:13 PM

This question kindof extends the project described at http://www.phpfreaks...c,111785.0.html 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
file3_approved
file4_unapproved

#2 knowNothing

knowNothing
  • Members
  • PipPip
  • Member
  • 24 posts

Posted 20 October 2006 - 05:24 PM

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.

#3 neylitalo

neylitalo
  • Staff Alumni
  • Advanced Member
  • 1,854 posts
  • LocationMichigan, USA

Posted 21 October 2006 - 07:18 AM

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
http://nealylitalo.net - My personal website, and home of The Netizen's Journal.

#4 Ninjakreborn

Ninjakreborn
  • Members
  • PipPipPip
  • Information Technology Specialist
  • 3,922 posts
  • Age:33

Posted 21 October 2006 - 05:21 PM

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.

------

Business Website: http://www.infotechnologist.biz

Personal Website: http://www.joyelpuryear.com

Blog Site: http://www.realmofwriting.com
Services: Web development, application development, mobile development, and custom development. All services listed on my website.


#5 knowNothing

knowNothing
  • Members
  • PipPip
  • Member
  • 24 posts

Posted 21 October 2006 - 06:28 PM

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

#6 knowNothing

knowNothing
  • Members
  • PipPip
  • Member
  • 24 posts

Posted 21 October 2006 - 06:32 PM

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.


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

#7 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 22 October 2006 - 10:04 AM

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


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.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#8 knowNothing

knowNothing
  • Members
  • PipPip
  • Member
  • 24 posts

Posted 23 October 2006 - 05:09 AM

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


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.


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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users