0

I am currently writing an ASP.NET MVC website which is using SQL Server 2008 database as its backend. There are few tables (like, Product catalog, etc) which should have thumbnail and full image, to be displayed on the website for the end user. I am having a confusion that whether I should use - A) FILESTREAM columns for storing images in database itself B) or simple varchar columns containing path and filename (or URL) of the images

What is the general practice? I know FILESTREAM will bring lot of code and DBA overhead with it, but if its the general practice than I am flexible to use it. Or else, storing URLs is definitely the easiest option. But the one which is recommended should be followed.

Any advice on this, much appreciated.

Thanks

Nirman
  • 6,715
  • 19
  • 72
  • 139

1 Answers1

2

I've never really understood the hype in storing images as binary blobs in the database. Let's keep in mind that your filesystem is a database designed for storing binary blobs, called files.

This question provides a lot of insight into the topic. I'd recommend everyone read it and its answers.

Personally, I've always stored images in the filesystem (images are files, after all!), and only store in the database the information required to generate a URL at runtime.

One scheme I've seen is to use the MD5 of the image as its filename, and store that MD5 in the database with the image ID. Then you can break down the MD5 hex digits into several components to build a directory structure. This limits the number of files in a single directory.

For example, an image might have an MD5 of d41d8cd98f00b204e9800998ecf8427e. Its entry in the database would look like this:

 id | md5                              | upload_date | ...
 ----------------------------------------------------------
 42 | d41d8cd98f00b204e9800998ecf8427e | 2013-04-01  | ...

And this file might be stored at images/d4/1d8/cd98f00b204e9800998ecf8427e.jpg. Where you place the path separators and how many of them you have determines the balance between the number of directories and number of files in each directory.

Community
  • 1
  • 1
Jonathon Reinhart
  • 132,704
  • 33
  • 254
  • 328
  • I agree with Jonathon.. Actually I think that storing the images in the database will give you more overhead than the possible real benefits (if any) you might get. And even more, you won't be able to get the benefits of having a CDN – pollirrata Apr 01 '13 at 23:50