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

The prof said "store the menu information in the database". "menu information" obviously includes food names, prices, descriptions, all of which are textual. He may or may not have meant the pictures as well. This may be a case of communication problems. ...databases are used when the information is expected to change often; they require an output display subsystem, and an input insertion subsystem. If you're only going to write it once, by all means put the pics in a directory. If, however, it's going to be a system where people edit and insert new entries, then either you have to couple the pictures to the menu entries in the same DB table row (simpler to write), or you have to write a different subsystem to manage both the database and also now the files on disk, along with their permissions (more moving parts).

Answer 2

File systems have been fine-tuned for ages for the task of delivering files quickly and efficiently. Not utilizing the file system for storing images would be ignoring one of its greatest abilities.

In my opinion, the perfect synthesis between storage images in the database and filesystem is to store the actual file on the disk using a filename concated with a timestamp, and then store that reference in the database with its associated row.

Answer 3

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)

Answer 4

Saving images in the Database is usually a bad idea, and I dont quite get, where you are stuck. Just save the path of the menu image for each menu item and you are good to go.