[ACCEPTED]-Best way storing binary or image files-store

There is no real best way, just a bunch 32 of trade offs.

Database Pros:
1. Much easier to deal with 31 in a clustering environment.
2. No reliance 30 on additional resources like a file server.
3. No 29 need to set up "sync" operations in load 28 balanced environment.
4. Backups automatically 27 include the files.

Database Cons:
1. Size / Growth of 26 the database.
2. Depending on DB Server 25 and your language, it might be difficult 24 to put in and retrieve.
3. Speed / Performance.
4. Depending 23 on DB server, you have to virus scan the 22 files at the time of upload and export.

File Pros:
1. For 21 single web/single db server installations, it's 20 fast.
2. Well understood ability to manipulate 19 files. In other words, it's easy to move 18 the files to a different location if you 17 run out of disk space.
3. Can virus scan 16 when the files are "at rest". This allows 15 you to take advantage of scanner updates.

File Cons:
1. In 14 multi web server environments, requires 13 an accessible share. Which should also 12 be clustered for failover.
2. Additional 11 security requirements to handle file access. You 10 have to be careful that the web server and/or 9 share does not allow file execution.
3. Transactional 8 Backups have to take the file system into 7 account.

The above said, SQL 2008 has a 6 thing called FILESTREAM which combines both 5 worlds. You upload to the database and 4 it transparently stores the files in a directory 3 on disk. When retrieving you can either 2 pull from the database; or you can go direct 1 to where it lives on the file system.

Pros of Storing binary files in a DB:

• Some decrease in complexity since the data access layer of your system need only interface to a DB and not a DB + file system.
• You can secure your files using the same comprehensive permissions-based security that protects the rest of the database.
• Your binary files are protected against loss along with the rest of your data by way of database backups. No separate filesystem backup system required.

Cons 14 of Storing binary files in a DB:

• Depending on size/number of files, can take up significant space potentially decreasing performance (dependening on whether your binary files are stored in a table that is queried for other content often or not) and making for longer backup times.

Pros of 13 Storing binary files in file system:

• This is what files systems are good at. File systems will handle defragmenting well and retrieving files (say to stream a video file to through a web server) will likely be faster that with a db.

Cons 12 of Storing binary files in file system:

• Slightly more complex data access layer. Needs its own backup system. Need to consider referential integrity issues (e.g. deleted pointer in database will need to result in deletion of file so as to not have 'orphaned' files in the filesystem).

On 11 balance I would use the file system. In 10 the past, using SQL Server 2005 I would 9 simply store a 'pointer' in db tables to 8 the binary file. The pointer would typically 7 be a GUID.

Here's the good news if you are 6 using SQL Server 2008 (and maybe others 5 - I don't know): there is built in support 4 for a hybrid solution with the new VARBINARY(MAX) FILESTREAM 3 data type. These behave logically like VARBINARY(MAX) columns 2 but behind the scenes, SQL Sever 2008 will 1 store the data in the file system.

There is no best way.

What? You need more 42 info?

There are three ways I know of... One, as 41 byte arrays in the database. Two, as a 40 file with the path stored in the database. Three, as 39 a hybrid (only if DB allows, such as with 38 the FileStream type).

The first is pretty cool because 37 you can query and get your data in the same 36 step. Which is always nice. But what happens 35 when you have LOTS of files? Your database 34 gets big. Now you have to deal with big 33 database maintenance issues, such as the 32 trials of backing up databases that are 31 over a terabyte. And what happens if you 30 need outside access to the files? Such 29 as type conversions, mass manipulation (resize 28 all images, appy watermarks, etc)? Its 27 much harder to do than when you have files.

The 26 second is great for somewhat large numbers 25 of files. You can store them on NAS devices, back 24 them up incrementally, keep your database 23 small, etc etc. But then, when you have 22 LOTS of files, you start running into limitations 21 in the file system. And if you spread them 20 over the network, you get latency issues, user 19 rights issues, etc. Also, I take pity on 18 you if your network gets rearranged. Now 17 you have to run massive updates on the database 16 to change your file locations, and I pity 15 you if something screws up.

Then there's 14 the hybrid option. Its almost perfect--you 13 can get your files via your query, yet your 12 database isn't massive. Does this solve 11 all your problems? Probably not. Your 10 database isn't portable anymore; you're 9 locked to a particular DBMS. And this stuff 8 isn't mature yet, so you get to enjoy the 7 teething process. And who says this solves 6 all the different issues?

Fact is, there 5 is no "best" way. You just have 4 to determine your requirements, make the 3 best choice depending on them, and then 2 suck it up when you figure out you did the 1 wrong thing.

I like storing images in a database. It makes it 5 easy to switch from development to production 4 just by changing databases (no copying files). And 3 the database can keep track of properties 2 like created/modified dates just as well 1 as the File System.

I personally never store images IN the database 14 for performance purposes. In all of my 13 sites I have a "/files" folder where I can 12 put sub-folders based on what kind of images 11 i'm going to store. Then I name them on 10 convention.

For example if i'm storing a 9 profile picture, I'll store it in "/files/profile/" as 8 profile_2.jpg (if 2 is the ID of the account). I 7 always make it a rule to resize the image 6 on the server to the largest size I'll need, and 5 then smaller ones if I need them. So I'd 4 save "profile_2_thumb.jpg" and "profile_2_full.jpg".

By 3 creating rules for yourself you can simply 2 in the code call img src="/files/profile__thumb.jpg"

Thats 1 how I do it anyway!