Is it better to store a lot of images and pdf files in a database or to just store text links to those files and store the files on the web server?

My company has hundreds of images and pdf documents that we want to be accessible through a MySQL database and served to the web via PHP.

So which is the better way to go?

Thanks..

Brian

There doesn';t seem to be a right or wrong answer to this.

You can either store the files on the server and link to them from the database or simply store them in the database.

Of course with the latter you just end up with a huge database file.

It really depends on what you are trying to achieve with it. For instance you could argue that you can have better security for the files (if they need to be secure) if they can only be retrieved with a php script (but then you can do the same thing by storing the files outside the document root).

However you can't perform image functions on images stored in databases e.g. GetImageSize()

You can do either however I generally recommend not storing binary files in a database, the reason being that you don't really need to store them in a database.

Filesystems were designed to store files and they do it very well.

If you store a file in a database you are going to consume database space, if you store a lot of files your database perfomance will suffer, and every time someone wants to retrieve the file they will need to access the database, this will create additional overhead for your database server and reduce it's ability to do actual database work.

If you need to track files I recommend storing the filename in a database table and then referencing them off the file system if needed, you might still hit the db server to find the file name but transfering a couple of KB off your db server will be much less of a problem than transferring megabytes.

I use small images in a database for one reason.

I have a number of sites pulling a generic source of images. However I want all images to come from the same domain name as the site is on rather than one common domain name (I know I could open the file but I don't want to turn safe mode off)

I don’t recommend storing the binary files into a database because it will be slow and I think it is dangerous.

I'm the same opinion as Ice_Black, databases are just not made for that purpose. You have absolute security at well configured filesystem/webserver. I think db will sufffer badly doing that.

If you return the image from db through script there is waste of db resources cos you can just link to file (for example password proteced if you need that)

-DeX

There is no right or wrong, it's a matter of needs

Yes, storing binary data in a database can increase overhead, BUT, if your application requires you to maintain absolute certainty that this data can not be modified without the appropriate rights, then it makes sense to place it in your database. So, it comes down to a trade-off between functionality/requirements and performance/simplicity.

(examples taken from a post on devshed forums)
For example:

1. A legal document management system, which maintains relationships between scanned documents and cases as they progress. In this case, it absolutely makes sense to keep the data in the database. All the data is controlled from your central data constraints system, which can help make sure that someone doesn't accidentally overwrite a file on the filesystem, etc... (also, keeping the data in the database allows for easier management of such functionality as versioning/rollback, timestamping each access to the document, etc...)

2. A website which lists thousands of products to be sold online, and ties the product details in with thumbnails and full-size pictures. In this case, the danger is not so great if an image is missing (so there is no picture of the product temporarily, Oh well...), so for performance's sake, you would probably be justified in keeping your images in the filesystem.

Originally posted by Quickfoot
[...] Filesystems were designed to store files and they do it very well.

If you store a file in a database you are going to consume database space, [...]

Where do you think a database stores its data? In wonderland?
No, in files. Good old files in a good old filesystem.

But yes, it eats valuable database space if that space is limited, eg. if you don't have a dedicated server or something like that.
Of course, it slows down the MySQL server, especially if it's hosted on either a different harddisk or another server. The time which is needed to read the image can be used better for actual data being read from the db.
That's why I see no use in storing images in the db, the webserver's partition is a much better place for them.

in shared hosting enviroment, where I allow file upload, I much prefer storing uploaded images in the database. This way I do have control over what goes in and what goes out. I do not fear that someone will obuse my 777 chmoded directory. As far as getting image properties (e.g getimagesize() ) you do all that stuff before you dump image in the database and store it with the image. Only perfomance issue here migh be additional connections to the databse. Only effect on the database perfomace here woud be greater database size and that would show up only durring db server start up.

"Only effect on the database perfomace here woud be greater database size"

No, as I stated above, during the querys which read all those images the server is tied up and can't do other things.

<begin sarcasm>
hmm, it would be cool if there would be
such thing as threads, so mysql could do more then one thing in the same time. Then to these threads the same limits would apply as the ones that apply to the file system (maximum of opened file descriptors), so it would make no differece where your files are stored.
</end sarcasm>