To store in DB or not to store in DB, that is the question.

DuneMessiah

Hi,

I would like to collect some opinions on the following topic:

It's yet another photo album application...

I wonder if I should store photos in MySQL or only store their location into MySQL.

What's, in your opinion, the best way to proceed?

Cheers,

stolzyboy

Location, Location, Location // faster and easier to work with

IMHO

Good Luck

Traduim

Completely agree. Only locations in DB.

DuneMessiah

Thanks to both of you!

So, no one to speak in favor of storing photos into MySQL? 😃

I know several photo album applications that do that.
Some often say it's easier to maintain DB integrity if you put the photos into MySQL.

Anyone to defend that position?

stolzyboy

i am not defending the position you are speaking of, i am arguing
against it:

that statement is dumb, why would you want to place large files or BLOB (binary data) in your database rather than a simple text file path, that seems dumb to me, storing simple text references to the image/file in the directory structure will keep integrity in store rather than the BLOB method

superwormy

The MySQL manual specifically recommends AGAINST storing images ( BLOB data ) in the database if you can just store the filepath instead.

DuneMessiah

Originally posted by stolzyboy
i am not defending the position you are speaking of, i am arguing
against it:

that statement is dumb, why would you want to place large files or BLOB (binary data) in your database rather than a simple text file path, that seems dumb to me, storing simple text references to the image/file in the directory structure will keep integrity in store rather than the BLOB method

I am on your side here.

I was trying to verify I am still sane.

Since there is no one to argue that photos should be stored in the database, I will not do so.

DuneMessiah

Originally posted by superwormy
The MySQL manual specifically recommends AGAINST storing images ( BLOB data ) in the database if you can just store the filepath instead.

Do you have a link for that reference?

superwormy

Of course:

When using a normal web server setup, images should be stored as files. That is, store only a file reference in the database. The main reason for this is that a normal web server is much better at caching files than database contents. So it it's much easier to get a fast system if you are using files.

From this page:
http://www.mysql.com/doc/en/Tips.html

DuneMessiah

Thanks, superwormy.

That's great.

ahundiak

I am not really defending the idea of storing photos directly in a database but it does have some advantages. You don't need to worry about the file system anymore, just a single database interface does the all the work.

No more concerns about unix paths vs windows paths. No more worrying about spaces or other special characters in a file name or file name lengths.

If the photos are being shared with other applications then the other apps only need access to the database and not the file server.

Using only a database is probably a bit more secure than using both a database and a file system. And perhaps a bit easier to manage and backup.

The web hoster I use has a limit on disk space but (so far) no limit on database size. Of course, I suspect that may change if I suddenly stored a few hundred GB's of pictures in the database.

stolzyboy

well, the path thing isn't a real valid argument, since you can use relative paths regardless of the server you are on.

you don't have to worry about spaces if you are renaming files as you upload anyway, so i wouldn't worry about that either.

worrying about the file system is negligible compared to a database, much easier to fix file system than trying to recover a corrupted database.

security is irregardless in this situation, if they can tap a file system, they will most likely be able to tap a mysql database, since all a mysql database and most others are simply folders with different files in them.

and most web hosts will count all used space, regardless of where it is, in database, file system, or email accounts

i don't know about ahundiak, but i have built many systems with the location way, and it is much easier to work with.

i have a friend who tried the other and all he ran into was troubles. he tried this suggestion after using the location method and was thoroughly disgusted at the drawbacks of storing files in the database itself.

Remember this is my opinion, but i do have the experience in this, but others may have different experience, and remember MySQL suggest NOT to store files in the database, and who better to tell you that than the creators of the database.

at any rate good luck on your venture