[forrestyuan]

I have two options:

1. Store the photographs in a blob column.
2. Save each photograph in a file, and store the file name in a column.

Which one is better?

[andrewst]

Originally posted by forrestyuan
I have two options:

1. Store the photographs in a blob column.
2. Save each photograph in a file, and store the file name in a column.

Which one is better?

From a logical viewpoint, I'd prefer to have the data in the database along with all the other data. That way it is protected and backed up the same way as all the other data.

From a technical viewpoint, I don't know the pros and cons.

[sco08y]

Originally posted by forrestyuan
I have two options:

1. Store the photographs in a blob column.
2. Save each photograph in a file, and store the file name in a column.

Which one is better?

I've done an application that stored user files in the file system.

Writing code to sift through directory listings is just a pain. Writing extra code to do the backups is, again, a pain. The files will inevitably fall out of synch with your tables, more code to clean that up. You can run into conflicts with poorly designed file systems if you try to put them all into one huge directory, and coming up with schemes to store them in multiply nested directories is a pain. You need to worry about setting permissions on all those files, more configuration headaches, especially with Windows. You have to special case your mechanism to handle files. If you want to be network transparent, you have to connect via file sharing, which means more configuration.

Is that enough work to discourage you from this? Are there any advantages...

One sort of possibility: if you're running a virus scanner, you can have it check uploaded files automatically. That might be sort of useful, except that there's no simple way of getting that information back to the user in a timely manner.

If your DBMS is using raw device files, it shouldn't have any speed disadvantage. Even if it's not, loading a file all at once is still pretty efficient.

If you have to do processing on the file, you'll have to copy it from the DBMS to a file and back, which is two extra passes.

It's *still* so much easier in terms of logistics...

[KillaCrab]

i storngly agree with sco08y. I prefer using option 1. given the fact that it is easier to manage (as earlier stated by sco08y) and requires less headaches.

it is easier distributing images(binary files) this way. no need to give anyone access to my directories just access to the database/application would be sufficient.