How to add image support to client-server database application?

Question

I have an architectural question about a project that I am working on.

Currently it is a simple .NET C# application that runs on several client machines, and communicates with a central MySQL server for data storage. There is not an application or service on the server at this time, just MySQL.

I would like to add image support so that users can add images to the database (and view after the fact), but after storing the images directly in MySQL as BLOB I've decided this approach is a bad idea, as suggested in many posts on SO.

The application can't store images on the local filesystem because they would then be inaccessible to other users not on the same local host.

That being the case, I'm not sure how best to implement image support otherwise:

• The machines are all Windows-based, I can set up a shared folder but then I have the headache of maintaining a mapped network drive and user credentials/permissions.
• I've thought of adding another application (or service) that runs on the server and handles reading/writing of images on the server side, but this seems redundant in light of the fact that the computers already are capable of file transfers.
• Should I consider creating an application on the server to handle all communications between clients and the SQL database, adding filesystem communication there?

Answer 1

Storing images in the database is not always a bad idea. If you have a small amount of images, say in the tens of thousands range, and they are small in size, sometimes the convenience of storing the images in the database outweighs the added complexity introduced by adding an additional storage server that needs to be written and supported into the mix. Is storing the image in the db the most efficient means? No. Does that matter if you only have 10 client applications accessing a couple hundred images a day? Probably not. In that case, live with the inefficiency and save yourself a lot of effort.

If you have a lot of images (millions, tens of millions,...I've worked on systems that track billions), then you'll want a seperate server application that can handle storage and retrieval of files. At the most basic level this can be (for example) simply a tcp/ip server app tha can handle requests for files, as well as accept them from across the network (a web server running a PHP script could be adapted for this). You'd just access the images based on an id in the database for a row that stores metadata about that image. Remember, if this is across a public wire, you'll need to implement your authorization system to access the images. More complicated servers might handle mirroring, file type conversions, and the migration of files to and from longer term (and cheaper) storage mediums.

Answer 2

I suggest reading To BLOB or Not To BLOB - it is a Microsoft Research paper regarding this issue.

They tested on SQL Server 2008 and concluded that if most files are under 150kb, storage in the DB is suitable.

Of course, you should test for your own environment and database.

Answer 3

Best solution i would suggest is that upload (or store) all images behind a web server and attach each resources with a unique id (or URL) which can be stored inside the DB.

Because it is behind a web server, it can become accessible via a direct fetch will not load database server to pull the image out from it. Also, using web based (URLs) - you are not limited by the physical space of a given hard disk or worry about the redundancy, load balancing etc. because web based mechanisms are quite well established to allow massive redistribution.

Also, you can extend end-user based authentication for actual access.

Answer 4

The application can't store images on the local filesystem because they would then be inaccessible to other users not on the same local host.

1 - Storing the images in the database is not a great idea. However, you can store them on a separate table from your regular data in a compressed format.

2 - You can store the images on the database server file system and use FTP to access them.

3 - If this is not what you want, you'll need to use a server (intranet/internet) where you can store those images. You may also require a service to use for storing and fetching the images in a secured meaner.