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?
Best Answer
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.