2

I’m developing a web-based store in MVC (very closely based on the SportsStore example in Pro ASP.Net MVC 3 Framework http://www.powells.com/biblio/61-9781430234043-0).

I’m using a SQL database and accessing it through System.Data.Entity.DbContext

I have a table that describes the products that I’m selling, with columns for name, description, price – this seems to work fine.

However, I also want to include images. I’ll probably have between 3 and 6 images per product that are about ~150KB each.

I think it would be convenient to store these images as binary data into another table in the database and include a column that identifies which product they’re related to. This should work but it seems inelegant – do you have a better suggestion?

I’d really like to keep the images in the database, that seems easier than storing paths to images in the database and separately uploading the images to a webserver.

Improve this question

5 Answers 5

Reset to default
6

I would seriously recomend you keep your images in a folder under the root directory of your web server ("htdocs" or equivalent) and store a reference to the file name in your database.

That way you can select the file reference from the database and copy it into an html "img" tag and let your webserver and browser do the heavy lifting.

There are several advantages to this:-

  • smaller more efficient database.
  • much faster sql response -- no large blobs to handle
  • much simpler program code
  • faster response times for your users as the web server will serve image files faster than you can stream them.
  • much faster response times if they re-visit your site because many of the images will be cached in their browser.
Improve this answer
1
  • Another benefit is that the images could go on another server, which will scale much better
    – David_001
    Commented Jun 14, 2012 at 7:48
5

Storing binary image data in a database is not usually the best idea. See this Stack Overflow question. If you are going to have a lot of images it looks like using the filesystem will scale better, but that's something you could benchmark on your own to be sure. I'm also not sure that it is any easier to store the images in the database compared to just using the filesystem.

As for modeling the data, your inclination to have a separate images table with a foreign key to the product doesn't seem inelegant at all.

Improve this answer
2

If you're talking low to medium numbers of images (say, under a couple or ten thousand) and low to medium traffic, its just not going to matter much where you put them. Either will work.

There are some significant benefits to storing them in the file system, though, especially for a web app. Primarily that the image is servable without a database connection. So you wont need a script to extract the image out of the database and serve it up. For low levels of traffic, reading blobs wont have a noticable impact. But under high load, you'll quickly see things slowing down as the database server is spending all its time and effort retrieving images.

Storing in the filesystem is not difficult. Set up a path where you'll store them (/images). Keep a table in the db that stores their metadata (name, product id, etc). And then save the files there based on their unique row id in that table. 1.jpg, 2.jpg, etc. This is very easy to do if you have a web-based image upload in your app. If you end up with many images, you just create a new directory for each 'thousands', so '4000.jpg' is stored in '4/4000.jpg'.

Improve this answer
1

A key characteristic of a successful shopping site is speed. Serving images from the database is slow. So storing images on the web server's file system is something of a must.

Having said that, there are several workarounds. What we like to do is to store (not serve) the images in the database. We run a scheduled job on the web server that reads the images in the database and stores them locally. That way, the images are part of the database backup, and updating them is really easy. If you write the job efficiently, it can run once a minute, so the delay is not much of a problem.

Improve this answer
0

SQL Server 2008 FILESTREAM option for a VARBINARY(MAX) column is the best of both worlds. The images are still saved to a file to help with performance, but your code will just save to a table and the database handles putting files to disk. This can handle large images as well as high traffic.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.