php - When to store images in a database(mySQL) and when not? (Linking image to item in database)

Question

Sorry if this is not the type of question to be asked here but I read through the FAQ and it seemed like it should be fine.

Background info: As apart of a graduating project, my group is creating a site for a restaurant that will have a menu shown. The menu will have mouse-over functions to display an image of the food when the user mouseover's the item name. My original plan was to not store the menu information in a database being that it is not going to change very often. There are two different locations with different menu items. So all in all to me it seemed like it would be a waste to store all that info in a database and just display it using HTML. Our professor who is not very tech savvy beyond what was used 15 years ago, informed us he knows it would be better to store this info in a database. To me this seems like it would really confuse things a lot with no added value. Also originally I planned on storing the images for the mouseover in a simple folder on the server but if all the menu items will be stored in the database I wouldn't know how to link the different images to the different items in the database without also storing the images in the database. The site will mainly use html, php, mysql and some javascript We will also be developing an android application to just basically mimic what is on the website, if that changes things at all.

The question- Would this be something that would be better to be stored in a database in reference to the way i plan on using them? If so, Is there a way to do this without storing the images in the database and keeping them in a folder on the server or vice versa.

I have read a lot online in other forums explaining that you typically want to refrain from storing images in a database but since my professor thinks he is right in saying that the menu has to be stored in the database, I assume I would have to store the images in the database as well so the mouseover function will work properly. If this is even possible, I have only used mouseover functions with image locations on a server not from referencing the database. Thanks for all your help and insight.

Answer 1

Advantages of storing images/blobs in the database

• Storing the images is automatically part of your transactions
• No need to cleanup the file system and sync it with the database
• Access to the images is controlled through the same means as access to the rest of the data
• Only one single backup to care about
• A database is usually better suited to stored million (if not billions) of records. In the filesystem you have to find a clever distribution over several directories. A single directory will not be able to handle millions of files efficiently.
• For some workloads storing the images in the database could actually be faster.
  For SQL Server, Microsoft has actually tested this:
  http://research.microsoft.com/apps/pubs/default.aspx?id=64525
  I wouldn't be surprised if other databases behaved in a similar way.

Disadvantages:

• Makes your database and thus your backups a lot bigger (think of the time it takes to restore it).
• filesystems are usually better with incremental backups (unless you have Oracle's RMAN that is)
• the images cannot be access by other applications (e.g. a WebServer, image resizing tools, FTP Server)
• Images can't be spread across a content distribution network for load balancing in a web application (to take load off the application server)
• common belief is, that the retrieval from the database will be slower.
• for some databases retrieving the blobs might actually decrease the efficiency of the database cache (not so for Oracle, SQL Server and PostgreSQL. I don't know for MySQL though)